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: 
sculptorlv
Creator III
Creator III

Value from other column max value

Good day.

I have data:

Nr.     Date.                 Value

F1     15.04.2018          120

F1     17.03.2018          150

F1     01.06.2018          100

F4     18.06.2017          30

I need in LOAD statement get for each Nr. only Value from latest data:

Nr.     Date.                 Value

F1     01.06.2018          100

F4     18.06.2017          30

How can I do it?

Thank you in advance!

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

SET DateFormat='DD.MM.YYYY';


Data:

Load * Inline

[

Nr,  Date,      Value

F1,  15.04.2018, 120

F1,  17.03.2018, 150

F1,  01.06.2018, 100

F4,  18.06.2017, 30

F4, 20.06.2017, 10

];


Left Join(Data)

Load

Nr,

Date(Max(Date)) as MaxDate Resident

Data Group By Nr;


NoConcatenate

Final:

Load * Resident Data Where Date = MaxDate;


Drop Field MaxDate;

Drop Table Data;

View solution in original post

5 Replies
MK_QSL
MVP
MVP

SET DateFormat='DD.MM.YYYY';


Data:

Load * Inline

[

Nr,  Date,      Value

F1,  15.04.2018, 120

F1,  17.03.2018, 150

F1,  01.06.2018, 100

F4,  18.06.2017, 30

F4, 20.06.2017, 10

];


Left Join(Data)

Load

Nr,

Date(Max(Date)) as MaxDate Resident

Data Group By Nr;


NoConcatenate

Final:

Load * Resident Data Where Date = MaxDate;


Drop Field MaxDate;

Drop Table Data;

sunny_talwar

Another possibility is to do a Right or Inner join

SET DateFormat='DD.MM.YYYY'

 

Data: 

Load * Inline 

Nr,  Date,      Value 

F1,  15.04.2018, 120 

F1,  17.03.2018, 150 

F1,  01.06.2018, 100 

F4,  18.06.2017, 30 

F4, 20.06.2017, 10 

]; 

 

Right Join(Data) 

Load Nr,  

Date(Max(Date)) as Date

Resident  Data

Group By Nr; 

shiveshsingh
Master
Master

May be this

Temp:load Date((Date#(Date,'DD.MM.YYYY')),'DD.MM.YYYY') as Date,Nr,Value inline [Nr,Date , Value

F1, 15.04.2018 ,120

F1, 17.03.2018 ,150

F1, 01.06.2018 , 100

F4, 18.06.2017,30];

inner join

T: load Date(max(Date#(Date,'DD.MM.YYYY')),'DD.MM.YYYY') as Date,Nr

Resident Temp

group by Nr;

sculptorlv
Creator III
Creator III
Author

Thank you All!

I will use the first construction

sculptorlv
Creator III
Creator III
Author

Can you please correct me with the syntax.

NoConcatenate  is underlined by red line ..