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
MVP
MVP

Re: Mapping or joining a field equal to a grouped field

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

Re: Mapping or joining a field equal to a grouped field

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

Re: Mapping or joining a field equal to a grouped field

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
MVP
MVP

Re: Mapping or joining a field equal to a grouped field

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

Re: Mapping or joining a field equal to a grouped field

Thanks!