Get the Record corresponding to the latest year/month field

[Vichuf1]

Hi ,

PFB table 

Customer	Material	Year	Year/Month	Volume
A	AA	2019	201910	10
A	AA	2019	201911	20
A	AA	2019	201912	22
A	BB	2019	201908	33
A	BB	2019	201909	38
B	AA	2019	201904	65
B	AA	2019	201905	34
B	BB	2019	201912	32
B	BB	2020	202001	56
B	BB	2020	202002	34
B	BB	2020	202003	78
B	BB	2020	202004	32
C	AA	2020	202002	54
C	BB	2020	202003	45
C	CC	2019	201906	34
C	CC	2019	201907	56
C	CC	2019	201908	87

Requirement: Get only the last Record corresponding to a particular Customer and Material  i.e the latest year/month field of above combination.

Required result:

Customer	Material	Year	Year/Month	Volume
A	AA	2019	201912	22
A	BB	2019	201909	38
B	AA	2019	201905	34
B	BB	2020	202004	32
C	AA	2020	202002	54
C	BB	2020	202003	45
C	CC	2019	201908	87

Thanks in Advance

[vunguyenq89 · Accepted solution]

Hi,

The expected result can be achieved as follows:

1. Create a temp table containing max Year/Month value for each combination of (Customer, Material)
2. Use Inner Keep load statement to apply the above temp table as a filter to the original table
3. Drop the temp table

Sample script:

Data:
LOAD * INLINE [
Customer	Material	Year	Year/Month	Volume
A	AA	2019	201910	10
A	AA	2019	201911	20
A	AA	2019	201912	22
A	BB	2019	201908	33
A	BB	2019	201909	38
B	AA	2019	201904	65
B	AA	2019	201905	34
B	BB	2019	201912	32
B	BB	2020	202001	56
B	BB	2020	202002	34
B	BB	2020	202003	78
B	BB	2020	202004	32
C	AA	2020	202002	54
C	BB	2020	202003	45
C	CC	2019	201906	34
C	CC	2019	201907	56
C	CC	2019	201908	87
] (delimiter is '\t');

Inner Keep (Data)
Data_filter:
LOAD Customer, Material, Max([Year/Month]) as [Year/Month] 
Resident Data
Group By Customer, Material;

Drop Table Data_filter;

Hope this helps!

BR,

Vu Nguyen

[Vichuf1]

Hi,

Thanks a lot Vu Nguyen for your time, Yup I too solved it with left join with the same result.

Used Script:

LOAD
Customer,
Material,
max([Year/Month]) as [Year/Month],
Customer&Material&max([Year/Month]) as key
Resident Data
Group By Customer, Material;

left join

LOAD
Customer&Material&num(max([Year/Month])) as key,

Year,

Year/month,

VOLUME
Resident Data; 

Regards

Vishwanath R V