topic Re: How to display only the most recent data for a table based on another table value in App Development

How to display only the most recent data for a table based on another table value

kfloresc — Sat, 16 Nov 2024 01:24:56 GMT

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

Re: How to display only the most recent data for a table based on another table value

edwin — Thu, 24 Sep 2020 19:32:39 GMT

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.

Re: How to display only the most recent data for a table based on another table value

kfloresc — Fri, 25 Sep 2020 16:42:23 GMT

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!

Re: How to display only the most recent data for a table based on another table value

FunWithData — Tue, 17 Oct 2023 17:58:00 GMT

I have a similar issue...

My data set looks like the attached.

Batch# has duplicates in it based on the latest date that the field MvmType was updated.

What I want is for this table to remove any record that isn't the latest date for Batch# so that I can see what the latest MvmType is for each Batch#