09-12-2023 03:17 PM
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]
09-14-2023 07:40 AM
@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
09-14-2023 10:00 AM
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