topic Re: Load if..... in QlikView

Load if.....

tomhovens — Sat, 09 Sep 2017 18:06:40 GMT

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

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

Re: Load if.....

maxgro — Sat, 09 Sep 2017 18:24:46 GMT

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;

Re: Load if.....

vinieme12 — Sat, 09 Sep 2017 19:26:07 GMT

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);

Re: Load if.....

its_anandrjs — Sat, 09 Sep 2017 20:12:38 GMT

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;

Re: Load if.....

effinty2112 — Sun, 10 Sep 2017 08:54:37 GMT

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

Re: Load if.....

tomhovens — Sun, 10 Sep 2017 09:04:21 GMT

Thank you all for helping me out!