Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

Load only Distinct and recent values only???

Hi All,

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

see below example for more clarity.

Input:

LocationMonthYearQty
A8201410
A8201410
B820145
B8201410
B820147
C8201412
C8201420

Output:

Location   Month    Year    Sum(Qty)

A                  8           2014     10

B                  8           2014      7

C                  8           2014      20

Thanks,

Ashok.

1 Solution

Accepted Solutions
MVP
MVP

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

1.png

Input:

load

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

inline [

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

];

Final:

NoConcatenate

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

Resident Input

where Peek(LocMonYear) <> LocMonYear

order by Id desc;

DROP Table Input;

3 Replies
MVP
MVP

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

1.png

Input:

load

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

inline [

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

];

Final:

NoConcatenate

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

Resident Input

where Peek(LocMonYear) <> LocMonYear

order by Id desc;

DROP Table Input;

Not applicable

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

Hi Grossi,

Thanks for ur help.

That is what i expected.

Thanks,

Ashok.

robert99
Valued Contributor II

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

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;