topic Re: Sort by Date and Remove Duplicates in QlikView

Sort by Date and Remove Duplicates

Anonymous — Fri, 24 Apr 2015 12:34:26 GMT

Hi,

I have a number field that has multiple scores on different dates, I need to show only the number with the latest date score, so we need to sort date in desc order and then show only the number and the score at the latest date, removing the duplicates of other dates.

Attached example in excel file

This is urgent, please reply asap

Thanks

Re: Sort by Date and Remove Duplicates

Gysbert_Wassenaar — Fri, 24 Apr 2015 15:30:08 GMT

Try this:

Result:

LOAD Number,

Score,

Date

FROM [comm161622.xlsx] (ooxml, embedded labels, table is Sheet1);

RIGHT JOIN

LOAD Number, max(Date) as Date

RESIDENT Result

GROUP BY Number

;

Re: Sort by Date and Remove Duplicates

marcus_sommer — Fri, 24 Apr 2015 15:36:20 GMT

You could use a load-statement like:

Load Number, max(date) From x group by Number;

to find your last date and use this for a filter or to join/map these date to your source-table. But you should also have a look on firstsortedvalue() with which you could pick directly within the gui.

- Marcus

Re: Sort by Date and Remove Duplicates

Anonymous — Mon, 27 Apr 2015 08:35:34 GMT

Thanks for the reply guys

Thanks Gysbert, I think your solution worked just fine, appreciate it

Re: Sort by Date and Remove Duplicates

Anonymous — Mon, 27 Apr 2015 15:25:58 GMT

Hi guys,

gwassenaar‌ mby‌ jagan‌

Need your help again, I noticed a few blanks in my data, so now the output should be the latest dated score, in case the latest date has a blank score, then the previous latest date score should be picked up. Desired output colored in Red, please reply asap.

Appreciate your help

Thanks

Number	Score	Date
1234		1-Apr-14
1234	2	2-Jan-14
1234	1	1-Jan-14
1111	3	31-Mar-14
1111	4	1-Apr-14
9990		15-Apr-14
9990	6	2-Jan-14
9990	7	1-Apr-14