topic Records with the max date in QlikView

Records with the max date

Anonymous — Sat, 16 Nov 2024 05:00:08 GMT

Hello, I hope for your help within this topic.

I have a table with the price records, where the end date is vital for me.

Due to behaviour of my database, the date 01.01.1753 means that there is no end date, therefore this date 01.01.1753 is logicaly larger than any other date like 14.07.2018.

I need to remove all records which are not actual. This mean that if a device with a certain type have many records, I have to keep only one the record with the latest end date (where 01.01.1753 is the lates one).

The result have to be like this:

😞 I can't find the necessary logic in qlik myself

Thank you in advance

Re: Records with the max date

Anonymous — Tue, 11 Dec 2018 08:13:05 GMT

I have a solution. maybe there is a smarter solution
but interesting Interpretation of latest date (1.1.1753)

My solution consists of 4 steps:
1) load data with actual data (1.1.1753)
2) load contract & type which had not been loaded in previous step and Group by max(Enddate)
3) add addtional data like STartedate and Price by left join
4) concatenate results

My results equals to your final table

TESTABC:
load * Inline [
Contract, Type, Start, End, Price
1, C-A, 14.07.2014, 01.01.1753, 45
1, C-C, 18.04.2017, 30.04.2017, 45
1, C-C, 01.05.2017, 01.01.1753,0
1, C-P, 20.11.2014, 30.04.2016, 65
1,C-P, 01.05.2016, 31.12.2016, 0
];

// first get dates with actual data
TESTDEF:
load
Contract&Type as %Key,
Contract,
Type,
Start,
End,
Price
Resident TESTABC
where End='01.01.1753';

// next get data with contract and keys which have no actual data
TESTXYZ:
noConcatenate
load
Contract,
Type,
max(End) as End
Resident TESTABC
where not exists (%Key, Contract&Type)
group by Contract, Type;

// combine remaining fields
left join(TESTXYZ)
load *
Resident TESTABC;

// append to actual data
Concatenate (TESTDEF)
load *
Resident TESTXYZ;

drop table TESTXYZ;
drop table TESTABC;

Re: Records with the max date

Anonymous — Tue, 11 Dec 2018 09:03:13 GMT

Help you for your answer.

Yes, such logic is in MS Dynamics 5 (to be more precise the value is 01.01.1753 0:00:00)

Unfortunatelly, this code gives missmatched results:

.. and to be honest, I don't get the whole your logic (%key constraction is totally unknows for me ).

Will look for qlik FAQ

Re: Records with the max date

jyothish8807 — Tue, 11 Dec 2018 11:24:30 GMT

Hi Ruslans,

May be try this:

A:
Load
Contract,
Type,
date(Date#(StDate,'DD.MM.YYYY'),'DD.MM.YYYY') as StDate,
date(Date#(EndDate,'DD.MM.YYYY'),'DD.MM.YYYY') as EndDate,
Price;
LOAD * INLINE [
Contract, Type, StDate, EndDate, Price
A, 11, 14.07.2014, 01.01.1753, 45
A, 22, 28.04.2017, 30.04.2017, 45
A, 22, 01.05.2017, 01.01.1753, 0
A, 33, 20.11.2014, 30.04.2016, 65
A, 33, 01.05.2016, 31.12.2016, 0
];

NoConcatenate
B:
Load
Contract& Type as Key3,
Contract,
Type,
StDate,
EndDate,
Price
Resident A
where match(EndDate,'01.01.1753');

NoConcatenate
C:
Load
Key,
Key&EndDate as Key2;
Load
Contract&Type as Key,
Date(Max(EndDate),'DD.MM.YYYY') as EndDate
Resident A
where not match(EndDate,'01.01.1753')
Group by Contract& Type;

inner join
Load
Contract&Type&EndDate as Key2,
Contract,
Type,
StDate,
EndDate,
Price
Resident A
where not match(EndDate,'01.01.1753');
Drop table A;

NoConcatenate
E:
Load
*
resident B;
Concatenate
Load
*
resident C
where Not Exists(Key3,Key);
drop Table C;
Drop Table B;

Re: Records with the max date

kjhertz — Tue, 11 Dec 2018 12:27:26 GMT

This is a bit tricky but you could solve it with an if else statement in an expression and then aggr by Type.. but that can result in slow performance if your data set is large. Better then to use set analysis and create two mutual exclusive parts within the same aggr expression:

=aggr(max({<Type={"=[End date]<>'01.01.1753'"}>} [End date]) & only({<Type={"=[End date]='01.01.1753'"}>} [End date]),Type)

The max and only part within the aggr will neither be valid at the same time so the "&" will concatenate our desired result with an empty string depending on the value of [End date].