Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MEBG93
Creator
Creator

Mapping or joining a field equal to a grouped field

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!

 

Labels (3)
1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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;

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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;

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
MEBG93
Creator
Creator
Author

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!

jonathandienst
Partner - Champion III
Partner - Champion III

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;

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MEBG93
Creator
Creator
Author

Thanks!