topic Session Variable Query Filter Not Working in Data & Schema Discussions

Session Variable Query Filter Not Working

mkrieger — Tue, 12 Sep 2023 22:17:35 GMT

I am trying to create a session variable that returns a specific column value (say column X) from the row with the most recent date value. However the filter expression in my query does not work at all and I am returning all X column values.

Why is my query expression not working?

query(
schema.table.x_column,
schema.table.date_column = max(schema.table.date_column)
)

returns [x_value1, x_value2, etc]

Re: Session Variable Query Filter Not Working

anurag — Thu, 14 Sep 2023 14:40:40 GMT

@mkrieger query function works at row level and aggregate functions will not work.

You need to aggregate data in a table and then query that table in query function.

Please review this video for a similar use case:

https://www.youtube.com/watch?v=9CdKnTh2CiM

Re: Session Variable Query Filter Not Working

Shashidhar — Thu, 14 Sep 2023 17:00:50 GMT

Hi @mkrieger ,

Here is the solution from my end.

1) I took sample data like below.

2) Before creating the session variable, created an IAT table because session variable is returns only string type values.

3) Creation of a session variable on date (from IAT table) using dateformat.

4) Finally, applied that session variable in the prompts.

Regards,

Shashidhar.S