topic Re: Comparison of dates while doing resident load in QlikView

Comparison of dates while doing resident load

arpitkharkia — Thu, 10 Aug 2017 10:14:09 GMT

Hi all,

I have two tables. Consider the following example.

TableA:

Load ID,

DateA,

ColA,

ColB

From ...;

Left join(TableA)

TableB:

Load ID,

DateB

From ...;

I want a final table where DateA is greater that DateB. Im tring the following:

TableC:

Load *

Resident TableA where (DateA-DateB)>0

Please note there are some tables being left joined to Table A before left joining TableA and TableB

Help appreciated!

Re: Comparison of dates while doing resident load

Kushal_Chawda — Thu, 10 Aug 2017 10:19:46 GMT

TableA:

Load ID,

DateA,

ColA,

ColB

From ...;

Left join(TableA)

TableB:

Load ID,

DateB

From ...;

New:

noconcatenate

LOAD *,

if(DateA>DateB,1,0) as Flag

resident TableA;

drop table TableA;

Final:

noconcatenate

LOAD *

resident TableA

where Flag=1;

drop table New;

Re: Comparison of dates while doing resident load

sasiparupudi1 — Thu, 10 Aug 2017 11:21:18 GMT

A solution could be like below

Map_DateB:
Mapping LOAD * Inline
[
ID,DateB,
];

TableA:
Load * Inline
[
ID,DateA,ColA,ColB
];

Final:
Load DateA,ColA,ColB
Resident TableA
Where
DateA>ApplyMap('Map_DateB',ID);

Re: Comparison of dates while doing resident load

arpitkharkia — Fri, 11 Aug 2017 08:45:58 GMT

Thank you for the response!