topic Re: qlikview load data only newest date in QlikView

qlikview load data only newest date

spikenaylor1 — Wed, 25 Nov 2020 16:16:04 GMT

trying to load data set

a record for a sample for a batch has been entered on 16 March 2020 with data

it was found that the data was wrong

another record was entered for same batch with the corrected data but on the 17 March 2020

both records now show in the source table.

How do I load the data but only ensuring the newest record for that entry is loaded.

I.e. I only want the record for the 17 March 2002 to load, not the 16 March 2020

obviously this applies for looking at all records where there may have been corrected info entered in the source tabel

field I am looking at is ResultEnteredOn

Many thanks for Looking

Re: qlikview load data only newest date

vamsee — Tue, 23 Jun 2020 16:11:03 GMT

You have first find the unique key column for the batch.

Then, try

A: Load *, Unique_key,//This key should not include date value Date, Unique_key&Date as Key_To_Join From Source; Right Join(A) Load Unique_key&Date as Key_To_Join ; Load Unique_Key, Max(Date) as Date Resident A Group By Unique_Key ; Drop Field Key_To_Join from A;

Re: qlikview load data only newest date

spikenaylor1 — Mon, 29 Jun 2020 10:13:32 GMT

Cant get this to work, it still shows two entries, cant see what is wrong.

A: Load LotXLotName, ResultEnteredOn, ResultNumericEntry, ResultSampleNumber, ResultSampleNumber&ResultEnteredOn as Key_to_Join // create a unique key consisting of the Sample Number and the date entered. From $(vQC_ODHData_Ext) (qvd); Right Join(A) Load ResultSampleNumber&ResultEnteredOn as Key_to_Join; Load ResultSampleNumber, Max(ResultEnteredOn) as ResultEnteredOn // Only get the newest Date Entered. Resident A Group By ResultSampleNumber; Drop Field Key_to_Join from A; // Remove the Unique Key as we do not need it anymore.

Re: qlikview load data only newest date

vamsee — Mon, 29 Jun 2020 14:21:54 GMT

I don't see an issue in the screenshot shared, as I see two ResultSampleNumber and you are grouping by ResultSampleNumber.

Are you looking for one record per LOT?

Re: qlikview load data only newest date

NitinK7 — Mon, 29 Jun 2020 15:02:59 GMT

Hi,

Try like below to get only max date(new records)

Tabale_Name:

Load Distainct

'your key fied name',

Max(Date filed) as Date Field

from file path

group by 'your key fied name';

inner join

Load *

from your file path;

Re: qlikview load data only newest date

spikenaylor1 — Mon, 29 Jun 2020 15:56:17 GMT

Hi there all, thanks for the help, I have tried the examples given to no avail.

Here is a fuller set of data.

So I have loaded from the source a mixed bag of test results for different tests.

This sample set of data is just for one Lot.

In this example Test 5 has had data entered twice. the newer ResultEnteredOn date is the row I want showing . I want to discard the 1st Test 5 row.

All other rows would remain.

For Test 1, all the results are calculated at the same time in the source. that is why there is 7 entries.

if the user noticed they had entered something wrong, there would be a newer set of 7 rows for Test 1 with a newer ResultEnteredOn date. I would as with test 5 want to discard the Older test 1 rows keeping only the newer data.

any ideas, Would I need to break the loading down to loading for tests and remove older rows there, then combine all filtered data together, or can it be done for this one table.

Many thanks for any help or advice given

Re: qlikview load data only newest date

vamsee — Mon, 29 Jun 2020 17:05:20 GMT

Based on the inputs, my first advice would be to use incremental load on the data source while extracting data from each LOT, rather than taking this approach.

Refer: https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

If I got this right, firstly, you want the check to happen based on each Lot, TestReportedName & ResultSampleNumber.

So LOT&TestReportedName &ResultSampleNumber would be your Unique Key.

Try

A: Load LotXLotName, Num(TimeStamp#(ResultEnteredOn, 'dd/MM/yyyy hh:mm:ss')) as ResultEnteredOn, ResultNumericEntry, ResultSampleNumber, LotXLotName&TestReportedName&ResultSampleNumber as Unique_Key, LotXLotName&TestReportedName&ResultSampleNumber&Num(TimeStamp#(ResultEnteredOn, 'dd/MM/yyyy hh:mm:ss')) as Key_to_Join From $(vQC_ODHData_Ext) (qvd); Right Join(A) Load Unique_Key&Num(TimeStamp#(ResultEnteredOn, 'dd/MM/yyyy hh:mm:ss')) as Key_to_Join; Load Unique_Key, Max(ResultEnteredOn) as ResultEnteredOn // Only get the newest Date Entered. Resident A Group By Unique_Key; Drop Field Key_to_Join from A; // Remove the Unique Key as we do not need it anymore.

Re: qlikview load data only newest date

vamsee — Mon, 29 Jun 2020 17:09:18 GMT

The Timestamp() ensures the date format stays intact and join happens perfectly

Re: qlikview load data only newest date

spikenaylor1 — Tue, 30 Jun 2020 08:21:52 GMT

till cant get anything to work.

I have provided some sample data

aa: LOAD * INLINE [ LotXLotName,ResultEnteredOn,ResultName,ResultNumericEntry,SampleStage,TestReportedName,ResultSampleNumber,LotXDueDate AB1234,01/06/2020 18:57:30,Test5,425.24,A,Test5,2921298,25/05/2020 00:00:00 AB1234,08/06/2020 07:59:14,Test5,361.75,A,Test5,2927646,25/05/2020 00:00:00 AB1234,01/06/2020 19:57:30,Test1a,98,A,Test1,2904981,25/05/2020 00:00:00 AB1234,01/06/2020 19:57:30,Test1b,24,A,Test1,2904981,25/05/2020 00:00:00 AB1234,08/06/2020 08:59:14,Test1a,97,A,Test1,2932587,25/05/2020 00:00:00 AB1234,08/06/2020 08:59:14,Test1b,35,A,Test1,2932587,25/05/2020 00:00:00 ]; /* This is what I need from the Data, but in reality I have lots of LotXLotName, and lots of different TestReportedNames. AB1234,08/06/2020 07:59:14,Test5,361.75,A,Test5,2927646,25/05/2020 00:00:00 AB1234,08/06/2020 08:59:14,Test1a,97,A,Test1,2932587,25/05/2020 00:00:00 AB1234,08/06/2020 08:59:14,Test1b,35,A,Test1,2932587,25/05/2020 00:00:00 */ A: Load LotXLotName, ResultName, ResultNumericEntry, SampleStage, TestReportedName, ResultSampleNumber, LotXDueDate, Num(TimeStamp#(ResultEnteredOn, 'dd/MM/yyyy hh:mm:ss')) as ResultEnteredOn, LotXLotName&TestReportedName&ResultSampleNumber as Unique_Key, LotXLotName&TestReportedName&ResultSampleNumber&Num(TimeStamp#(ResultEnteredOn, 'dd/MM/yyyy hh:mm:ss')) as Key_to_Join resident aa; Right Join(A) Load Unique_Key&Num(TimeStamp#(ResultEnteredOn, 'dd/MM/yyyy hh:mm:ss')) as Key_to_Join; Load Unique_Key, Max(ResultEnteredOn) as ResultEnteredOn // Only get the newest Date Entered. Resident A Group By Unique_Key; Drop Field Key_to_Join from A; // Remove the Unique Key as we do not need it anymore.

just cant see why it is not working

Re: qlikview load data only newest date

vamsee — Tue, 30 Jun 2020 14:07:21 GMT

My bad made a minor correction to the join script.

Right Join(A)
Load
Unique_Key&Num(ResultEnteredOn) as Key_to_Join
;

Your final script

aa: LOAD * INLINE [ LotXLotName, ResultEnteredOn, ResultName, ResultNumericEntry, SampleStage, TestReportedName, ResultSampleNumber, LotXDueDate AB1234, 01/06/2020 18:57:30, Test5, 425.24, A, Test5, 2921298, 25/05/2020 00:00:00 AB1234, 08/06/2020 07:59:14, Test5, 361.75, A, Test5, 2927646, 25/05/2020 00:00:00 AB1234, 01/06/2020 19:57:30, Test1a, 98, A, Test1, 2904981, 25/05/2020 00:00:00 AB1234, 01/06/2020 19:57:30, Test1b, 24, A, Test1, 2904981, 25/05/2020 00:00:00 AB1234, 08/06/2020 08:59:14, Test1a, 97, A, Test1, 2932587, 25/05/2020 00:00:00 AB1234, 08/06/2020 08:59:14, Test1b, 35, A, Test1, 2932587, 25/05/2020 00:00:00 ]; /* This is what I need from the Data, but in reality I have lots of LotXLotName, and lots of different TestReportedNames. AB1234,08/06/2020 07:59:14,Test5,361.75,A,Test5,2927646,25/05/2020 00:00:00 AB1234,08/06/2020 08:59:14,Test1a,97,A,Test1,2932587,25/05/2020 00:00:00 AB1234,08/06/2020 08:59:14,Test1b,35,A,Test1,2932587,25/05/2020 00:00:00 */ A: Load LotXLotName, ResultName, ResultNumericEntry, SampleStage, TestReportedName, ResultSampleNumber, LotXDueDate, (TimeStamp#(ResultEnteredOn, 'dd/MM/yyyy hh:mm:ss')) as ResultEnteredOn, LotXLotName&TestReportedName&ResultSampleNumber as Unique_Key, LotXLotName&TestReportedName&ResultSampleNumber&Num(TimeStamp#(ResultEnteredOn, 'dd/MM/yyyy hh:mm:ss')) as Key_to_Join Resident aa; Drop Table aa; Right Join(A) Load Unique_Key&Num(ResultEnteredOn) as Key_to_Join ; Load Unique_Key, Max(ResultEnteredOn) as ResultEnteredOn // Only get the newest Date Entered. Resident A Group By Unique_Key; Drop Field Key_to_Join from A; // Remove the Unique Key as we do not need it anymore.

Re: qlikview load data only newest date

vamsee — Tue, 30 Jun 2020 14:10:39 GMT

Couldn't help notice but the timestamps on your tests are the same.

Assuming this is just because of sample data, as the result is the same that of input. (I/p 6 rows, O/p 6 rows)

Re: qlikview load data only newest date

spikenaylor1 — Wed, 01 Jul 2020 15:45:28 GMT

I still cant get the solution provided to work, it still displays all the records.

per your question.

TestReportedName - for Test1

on the source DB, there are 5 entries for Test1, all are subtests calculated at the same time when the data is entered. The ResultName shows in my example test 1a and 1b, the resultNumericEntry values are different, but The TestReportedName and ResultSampleNumber and ResultEnteredOn values will be the same for the 2 rows in my example, in reality the 5 rows will be the same, except for the resultNumericentry.

Could I ask if you could provide a QVW file showing that the load script works, so I can try to understand why it doesn't on my load.

Many thanks for all the help.

Regards

Spikenaylor

Re: qlikview load data only newest date

vamsee — Wed, 01 Jul 2020 15:48:55 GMT

Here you go