topic Re: Value from other column max value in QlikView

Value from other column max value

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Value from other column max value

MK_QSL — Tue, 27 Mar 2018 15:24:47 GMT

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;

Re: Value from other column max value

sunny_talwar — Tue, 27 Mar 2018 15:37:19 GMT

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;

Re: Value from other column max value

shiveshsingh — Tue, 27 Mar 2018 17:11:33 GMT

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;

Re: Value from other column max value

sculptorlv — Wed, 28 Mar 2018 05:24:56 GMT

Thank you All!

I will use the first construction

Re: Value from other column max value

sculptorlv — Wed, 28 Mar 2018 05:38:12 GMT

Can you please correct me with the syntax.

NoConcatenate is underlined by red line ..