topic Re: Get the Record corresponding to the latest year/month field in App Development

Get the Record corresponding to the latest year/month field

Vichuf1 — Sat, 16 Nov 2024 04:12:55 GMT

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

Re: Get the Record corresponding to the latest year/month field

vunguyenq89 — Thu, 31 Oct 2019 10:10:13 GMT

Hi,

The expected result can be achieved as follows:

Create a temp table containing max Year/Month value for each combination of (Customer, Material)
Use Inner Keep load statement to apply the above temp table as a filter to the original table
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

Re: Get the Record corresponding to the latest year/month field

Vichuf1 — Thu, 31 Oct 2019 10:40:10 GMT

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