topic Re: Max date less than and equal to another table date in QlikView

Max date less than and equal to another table date

somacdc — Fri, 27 Dec 2019 10:19:44 GMT

I want to calculate the max date of following Table1 which is less than and equal to another table (Table2) date.

Table1:

ID	F_NUMBER	NAV_1
2011	3	4/3/2032
2011	5	9/3/2032
2011	5	12/3/2032

Table2:

ID	F_NUMBER	NAV_2
2011	3	9/3/2032
2011	3	10/3/2032
2011	3	11/3/2032
2011	3	12/3/2032
2011	5	9/3/2032
2011	5	10/3/2032
2011	5	11/3/2032
2011	5	12/3/2032

and this is what i want it to return.

ID	F_NUMBER	NAV
2011	3	4/3/2032
2011	3	4/3/2032
2011	3	4/3/2032
2011	3	4/3/2032
2011	5	9/3/2032
2011	5	9/3/2032
2011	5	9/3/2032
2011	5	12/3/2032

I'm using mapping but it doesn't work with F_NUMBER=3

Re: Max date less than and equal to another table date

Rodj — Sat, 28 Dec 2019 01:20:43 GMT

I tried the following, which I think matches what you've asked for in your question but doesn't match your example result. I can't work out what you are really asking for as for F_NUMBER = 5 I can't see any logic as to how you've determined the answer. You've asked for the max date from table 1, which would be 12/3, yet you've used 9/3 in all but one result record. There's no uniqueness in the other records with which to determine any other matching than what I've done.

[temp1]:
LOAD * INLINE
[
ID,F_NUMBER,NAV_1
2011,3,4/3/2032
2011,5,9/3/2032
2011,5,12/3/2032
];

[temp2]:
LOAD * INLINE
[
ID,F_NUMBER,NAV_2
2011,3,9/3/2032
2011,3,10/3/2032
2011,3,11/3/2032
2011,3,12/3/2032
2011,5,9/3/2032
2011,5,10/3/2032
2011,5,11/3/2032
2011,5,12/3/2032
];

Maxtemp:
NoConcatenate
Load Max(NAV_1) as maxNav1,
ID,
[F_NUMBER]
resident temp1
group By ID, [F_NUMBER];
Join
load ID,
[F_NUMBER],
[NAV_2]
Resident temp2;

Final:
NoConcatenate
Load
ID,
[F_NUMBER],
if(maxNav1 <= NAV_2, Date(maxNav1, 'D/M/YYYY'), NAV_2) as NAV
Resident Maxtemp;

drop tables temp1, temp2, Maxtemp;

Re: Max date less than and equal to another table date

somacdc — Mon, 20 Jan 2020 07:20:58 GMT

Thanks for reply.

Actually I'm asking for F_NUMBER = 5 from table 1 which have (suppose we have 8/3 also), 9/3 and 12/3

but from table 2 which have 9/3, 10/3, 11/3 and 12/3

so logic is that date of table 1 and table 2 :

9/3 is equal to 9/3 then result is 9/3,

8/3 and 9/3 is not equal to 10/3 then check less than 10/3 so,Max of both (8/3 and 9/3) and the result is 9/3.

so on.

following is the oracle query as Function used for your reference

SELECT MAX(NAV_1)
FROM table1
WHERE NAV_1 <= 10/3
AND ID = 2011
AND F_NUMBER = 5