Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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;

Community Browser