Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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 (4)
1 Solution

Accepted Solutions
Highlighted

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
Highlighted
MVP & Luminary
MVP & Luminary

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;

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Creator
Creator

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!

Highlighted

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

Highlighted
Creator
Creator

Thanks!