How to display only the most recent data for a table based on another table value
Hello everyone, I am rather new to Qlik Sense, I'll try to be clear in what I'm trying to do.
I have loaded a set of data from one table in the data load editor, however this set of data is missing some tables. I have been given access to another data source which contain the information I need and loaded this in the data load editor. The problem I have now it that this new data set has data for every single time the data was modified. Here's an example, lets say I need programX, in the first dataset programX exists with one entry, but is missing corresponding values for programY. The new dataset I loaded has programX and programY, but for dozens of timestamp entries. I need a way to only select the most recent.
In the Sheet section I have created a table with programX as a column and programY as another column. I now have dozens of rows and only need one. I found a table called programZ which has timestamps which I am trying to use to filter our and select only the most updated.
I can't figure out how to do this, I've tried firstsortedvalue, max(programZ). Please help lol
seeing the data would be helpful. even a small subset with all the X, Y, and Zs.
off hand, if you only need the latest values for a specific dimension, you can aggregate the table programY in load script, max(date) group by the dimension. then inner join this (you have dimension + date as keys) to itself (programY). anything that matches is the latest date, then add a flag and use that flag in your expression.
okay, I'm not allowed to share specifics but I'll substitute values out.
Data Load editor View:
lib connect to 'database 1';
Load program1,
program 2,
etc
SQL Select Program1,
etc
FROM 'Database1';
LIB CONNECT TO 'Database2';
LOAD Program1,
Program2,
etc,
SQL SELECT Program1,
Program2,
etc
FROM 'Database2';
Analyze Sheet View:
I inserted a table with coulmns for each program I loaded. The problem I'm having is that database1 is missing values for program2. In database2 I have the values for program2, however I have every single value we ever inserted each time it was updated and I only need the most recent. I don't know how to select only that to view on the Table on the sheet by default. I have program3, within Database2 that I can filter by, because it is date stamps. So I want to have the program2 column in my table select only the most recent associated value from the most recent timestamp value within program3.
I hope this makes things clearer, thank you for your help!
