topic Load only Distinct and recent values only??? in QlikView

Load only Distinct and recent values only???

Sat, 08 Nov 2014 05:57:45 GMT

Hi All,

I want load only distinct and recent values only while loading data.

see below example for more clarity.

Input:

Location	Month	Year	Qty
A	8	2014	10
A	8	2014	10
B	8	2014	5
B	8	2014	10
B	8	2014	7
C	8	2014	12
C	8	2014	20

Output:

Location Month Year Sum(Qty)

A 8 2014 10

B 8 2014 7

C 8 2014 20

Thanks,

Ashok.

Re: Load only Distinct and recent values only???

maxgro — Sat, 08 Nov 2014 08:25:03 GMT

Input:

load

*, Location &'-' & Month & '-' & Year as LocMonYear, rowno() as Id

inline [

Location, Month, Year, Qty

A, 8, 2014, 10

B, 8, 2014, 5

B, 8, 2014, 10

B, 8, 2014, 7

C, 8, 2014, 12

C, 8, 2014, 20

];

Final:

NoConcatenate

load Location, Month, Year, Qty, Id, LocMonYear

Resident Input

where Peek(LocMonYear) <> LocMonYear

order by Id desc;

DROP Table Input;

Re: Load only Distinct and recent values only???

Sat, 08 Nov 2014 09:54:05 GMT

Hi Grossi,

Thanks for ur help.

That is what i expected.

Thanks,

Ashok.

Re: Load only Distinct and recent values only???

robert99 — Sat, 08 Nov 2014 11:37:25 GMT

Here is another way of doing the same thing

I'm a big fan of not exists and use it often to eliminate duplicated entries

Or to load the first or last record etc

Input:

load

*,rowno() as ID

inline [

Location, Month, Year, Qty

A, 8, 2014, 9

A, 8, 2014, 10

B, 8, 2014, 5

B, 8, 2014, 8

B, 8, 2014, 7

C, 8, 2014, 12

C, 8, 2014, 20

];

Final:

NoConcatenate

load *,

Location AS ID2

Resident Input

where NOT EXISTS (ID2,Location)

order by ID desc;

DROP Table Input;

DROP FIELD ID2;