Load if.....

[tomhovens]

Hi Qlik'ers

i want to only load a row when it has the highest rownumber per date

Date	Item	RowNumber	Amount
01-09-2017	A	3	10
01-09-2017	A	2	15
01-09-2017	A	1	5
02-09-2017	A	2	12
02-09-2017	A	1	10

To

Date	Item	RowNumber	Amount
01-09-2017	A	3	10
02-09-2017	A	2	12

[maxgro · Accepted solution]

Table:

LOAD Date,

     Item,

     RowNumber,

     Amount

FROM

[https://community.qlik.com/thread/273990]

(html, codepage is 1252, embedded labels, table is @1);

RK:

Right Keep (Table)

LOAD Date,

max(RowNumber) as RowNumber

RESIDENT Table

GROUP BY Date;

DROP TABLE RK;

[vinieme12]

or maybe with Exists(),should be faster than Join or Keep when working with large datasets

RK:

LOAD Item&'_'&Date&'_'&max(RowNumber) as MaxRowKEY

FROM

[https://community.qlik.com/thread/273990]

(html, codepage is 1252, embedded labels, table is @1)

GROUP BY Item,Date;

Table:

LOAD Date,

     Item,

     RowNumber,

     Amount

FROM

[https://community.qlik.com/thread/273990]

(html, codepage is 1252, embedded labels, table is @1)

Where Exists(MaxRowKEY,Item&'_'&Date&'_'&RowNumber);

[its_anandrjs]

Try this with MAX() and Where Exists and Group By.

MainTable:

LOAD Date(Date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY') AS Date,Item,RowNumber,Amount,Date&Item&RowNumber as Key Inline

[

Date,Item,RowNumber,Amount

01-09-2017, A, 3, 10

01-09-2017, A, 2, 15

01-09-2017, A, 1, 5

02-09-2017, A, 2, 12

02-09-2017, A, 1, 10

];

tMax:

LOAD Date,Item, MAX(RowNumber) as MaxRow

Resident MainTable

Group By Date,Item;

MaxRow:

LOAD Date&Item&MaxRow as MaxKey Resident tMax;

DROP Table tMax;

NoConcatenate

Final:

LOAD * Resident MainTable Where Exists(MaxKey,Key);

DROP Table MainTable;

[effinty2112]

Hi Tom,

After loading your data in the script (I've called the data table Table1, change to the correct tablename) add these lines :

Inner Join(Table1)

LOAD Date, max(RowNumber) as RowNumber

Resident Table1 Group by Date;

giving:

Date	Item	RowNumber	Amount
01-09-2017	A	3	10
02-09-2017	A	2	12

Cheers

Andrew

[tomhovens]

Thank you all for helping me out!