Load only Distinct and recent values only???

[#1]

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.

[maxgro · Accepted solution]

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]

Hi Grossi,

Thanks for ur help.

That is what i expected.

Thanks,

Ashok.

[robert99]

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;