topic Re: reduce data with latest dates in QlikView

reduce data with latest dates

userid128223 — Fri, 19 Aug 2016 03:05:44 GMT

Data:

Date

City

Rates

02/Jan/2016

Calgary

12;34;55;67

16/Aug/2016

Calgary

33;78;94;23

1/Dec/2015

Surrey

98;23;55;11;23;55;66

23/May/2015

Calgary

66;45;22;134

2/Feb/2015

Surrey

09;33;56;74;83

03/May/2018

Niagara Falls

334;22;6;343;5

17/July/2014

Niagara Falls

55;32;55;77

How can I restrict data during script execution (not in table) to only load rows which matches latest dates for each City and show corresponding Rates.

Results

Date	City	Rates

16/Aug/2016	Calgary	33;78;94;23
1/Dec/2015	Surrey	98;23;55;11;23;55;66


03/May/2018	Niagara Falls	334;22;6;343;5

Re: reduce data with latest dates

vishsaggi — Fri, 19 Aug 2016 03:59:10 GMT

This would help you:

Table1:

LOAD

City,

Rates,

Date

FROM

[..\Desktop\LatestDates.xlsx]

(ooxml, embedded labels, table is Sheet1);

Filter:

INNER KEEP (Table1)

LOAD City,

Date(Max(Date), 'DD-MMM-YYYY') AS Date

Resident Table1

Group by City;

DROP TABLE Filter;