Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

tomhovens
New Contributor II

Load if.....

Hi Qlik'ers

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

DateItemRowNumberAmount
01-09-2017A310
01-09-2017A215
01-09-2017A15
02-09-2017A212
02-09-2017A110

To

DateItemRowNumberAmount
01-09-2017A310
02-09-2017A212
Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Load if.....

1.png

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;

5 Replies
MVP
MVP

Re: Load if.....

1.png

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
Esteemed Contributor II

Re: Load if.....

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.....

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;


OP1.PNG

effinty2112
Honored Contributor

Re: Load if.....

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-2017A310
02-09-2017A212

Cheers

Andrew

tomhovens
New Contributor II

Re: Load if.....

Thank you all for helping me out!

Community Browser