Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello experts,
I have this table
table1:
load*inline [
ID, Date, Country, department, 'Load Date', value
1, 02-02-2020, USA, Banking, 03-02-2020, 1000
1, 02-02-2020, USA, Banking, 23-02-2020, 1500
2, 02-02-2020, Canada, Marketing, 05-02-2020, 1500
2, 02-02-2020, Canada, Marketing, 21-02-2020, 1600
];
I need the max('Load Date') for group ID, Date, Country and Department, so I did:
MaxLoadDate:
load
max('Load Date') as MaxLoad,
ID, Date, Country, department
resident table1
group by ID, Date, Country, department;
Now I need to map or join the resulting MaxLoad into table1 like this:
table2:
load*inline [
ID, Date, Country, department, 'Load Date', value, MaxLoad
1, 02-02-2020, USA, Banking, 03-02-2020, 1000, null
1, 02-02-2020, USA, Banking, 23-02-2020, 1500, 23-02-2020,
2, 02-02-2020, Canada, Marketing, 05-02-2020, 1500, null
2, 02-02-2020, Canada, Marketing, 21-02-2020, 1600, 21-02-2020
];
Those MaxLoads that do not match with the inherent max('Date Load') will return null values.
I think that in SQL it is possible to do a left join doing a where 'Load Date' = MaxLoad. I need to do something like that.
Thanks!
If I've understood you correctly, then this is what you need:
table1:
load * inline [
ID, Date, Country, department, Load Date, value
1, 02-02-2020, USA, Banking, 03-02-2020, 1000
1, 02-02-2020, USA, Banking, 23-02-2020, 1500
2, 02-02-2020, Canada, Marketing, 05-02-2020, 1500
2, 02-02-2020, Canada, Marketing, 21-02-2020, 1600
];
Join(table1)
load
max([Load Date]) as MaxLoad,
max([Load Date]) as [Load Date],
ID, Date, Country, department
resident table1
group by ID, Date, Country, department;
You should try this code after your MaxLoadDate table.
Map:
Mapping load
ID&num(Date)&Country&department as ID,
MaxLoad
Resident MaxLoadDate;
Drop table MaxLoadDate;
Table2:
Load *,Applymap('Map',ID&num(Date)&Country&department,Null()) as MaxLoad
Resident table1;
Drop table1;
Hello Kaushik,
I've already tried that, but the resulting table is not what I expect:
table2:
load*inline [
ID, Date, Country, department, 'Load Date', MaxLoad, Value
1, 02-02-2020, USA, Banking, 03-02-2020, 23-02-2020, 1000
1, 02-02-2020, USA, Banking, 23-02-2020, 23-02-2020, 1500
2, 02-02-2020, Canada, Marketing, 05-02-2020, 21-02-2020, 1200
2, 02-02-2020, Canada, Marketing, 21-02-2020, 21-02-2020, 1300
];
See that the MaxLoad column its still taking into acount all Load Dates. What I need is a condition that matches MaxLoad with max(Load Date) within that group, regarthless if Value changes. Like:
table2:
load*inline [
ID, Date, Country, department, 'Load Date', MaxLoad, Value
1, 02-02-2020, USA, Banking, 03-02-2020, null, 1000
1, 02-02-2020, USA, Banking, 23-02-2020, 23-02-2020, 1500
2, 02-02-2020, Canada, Marketing, 05-02-2020, null, 1200
2, 02-02-2020, Canada, Marketing, 21-02-2020, 21-02-2020, 1300
];
Thanks!
If I've understood you correctly, then this is what you need:
table1:
load * inline [
ID, Date, Country, department, Load Date, value
1, 02-02-2020, USA, Banking, 03-02-2020, 1000
1, 02-02-2020, USA, Banking, 23-02-2020, 1500
2, 02-02-2020, Canada, Marketing, 05-02-2020, 1500
2, 02-02-2020, Canada, Marketing, 21-02-2020, 1600
];
Join(table1)
load
max([Load Date]) as MaxLoad,
max([Load Date]) as [Load Date],
ID, Date, Country, department
resident table1
group by ID, Date, Country, department;
Thanks!