Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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);
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;
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
Thank you all for helping me out!