Alt Function - Understanding

[erric3210]

Hi Community,

Can someone please let me know why Alt is returning Date2 instead of Date1.

Alt Function definition:

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Alt() returns the first valid number representation. 

I'm getting the wrong result:

Can someone please explain me the concept here.

Regards,

Eric

[marcus_sommer]

Your dates aren't dates else strings which look like dates because a date is always a number which may have a separate string-representation. I think the work of alt() would more be clear if you extend the expression like:

alt(date1, date2, 'no date')

To get your approach to work you need to ensure that your dates are recognized as dates, for example with:

alt(date(date#(date1, 'DD/MM/YYYY')) , date(date#(date2, 'DD/MM/YYYY')), 'no date')

[vinieme12]

Dates loaded  from inline load is being loaded as TEXT and not DATE

which is why alt() doesn't recognize values in Date1 and always returning the last parameter which is Date2

Convert String to Date as below

Load * ,  alt(Date#(Date1,'DD/MM/YYYY'),Date#(Date2,'DD/MM/YYYY')) as Date3    INLINE [

...

];

or you may just want to checkout COALESCE() as well

[MarcoWedel]

If you're interpreting Dates 1 and 2 anyways, you might also use a preceding load like:

LOAD *,
     Alt(Date1,Date2)			as Date3;
LOAD Date#(Date1,'DD/MM/YYYY')	as Date1,
     Date#(Date2,'DD/MM/YYYY')	as Date2
Inline [
Date1     , Date2
21/01/2022, 22/01/2022
22/01/2022,
          , 25/01/2022
26/01/2022, 27/01/2022
];