Comparison of dates while doing resident load

[arpitkharkia]

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!

[Kushal_Chawda · Accepted solution]

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;

[sasiparupudi1]

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);

[arpitkharkia]

Thank you for the response!