Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a if statement
if (STATUS = '2', date(Date#(DAT,'YYMMDD')) ,null()) as Status_Date,
However, I need to add another date format date(Date#(DAT,'YYYYMMDD')) in the else condition.
Is it possible?
Any help would be appreciative.
Do you really need to look for status 2? What if you do like this instead? First look for YYYYMMDD and if that does not return any value then try to look for YYMMDD.
LOAD
*,
ALT(date(Date#(DAT,'YYYYMMDD')),date(Date#(DAT,'YYMMDD'))) as Date,
//if (STATUS = '2', date(Date#(DAT,'YYMMDD')) ,date(Date#(DAT,'YYYYMMDD'))) as Status_Date
inline [
ID, STATUS, DAT, AMOUNT
1, 2, 160101, 10
2, 1, 20160101, 11
3, 2, 191231, 12
4, 1, 20191231, 14
];
Consider this example script:
LOAD
*,
if (STATUS = '2', date(Date#(DAT,'YYMMDD')) ,date(Date#(DAT,'YYYYMMDD'))) as Status_Date
inline [
ID, STATUS, DAT, AMOUNT
1, 2, 160101, 10
2, 1, 20160101, 11
3, 2, 191231, 12
4, 1, 20191231, 14
];
This is my actual script
if( STATUS = '3', date(Date#(DAT,'YYYYMMDD')) ,
if (STATUS = '5',date(Date#(DAT,'YYYYMMDD')) ,
if(STATUS = '2', date(Date#(DAT,'YYMMDD')),
if(STATUS = '4', date(Date#(DAT,'YYYYMMDD')) ,null())))) as Status_Date,
I need to add two difference date format in then condition for status =2
Sorry for the confusion.
The issue I have is that I have two different date format for status 2
One format is in 'YYMMDD' and another one is in 'YYYYMMDD'. Please see the attached pic
The logic is breaking because then condition for status 2 is only looking for this date format 'YYMMDD'. I want it to look for both format 'YYMMDD and 'YYYYMMDD'
if(STATUS = '2', date(Date#(DAT,'YYMMDD')),
Hope this make some more sense.
Do you really need to look for status 2? What if you do like this instead? First look for YYYYMMDD and if that does not return any value then try to look for YYMMDD.
LOAD
*,
ALT(date(Date#(DAT,'YYYYMMDD')),date(Date#(DAT,'YYMMDD'))) as Date,
//if (STATUS = '2', date(Date#(DAT,'YYMMDD')) ,date(Date#(DAT,'YYYYMMDD'))) as Status_Date
inline [
ID, STATUS, DAT, AMOUNT
1, 2, 160101, 10
2, 1, 20160101, 11
3, 2, 191231, 12
4, 1, 20191231, 14
];
Has, did Vegar's final post get you what you needed? If so, please be sure to come back to the post and use the Accept as Solution button on that post to give him credit and let others know did what you needed. If you are still working on things, please leave an update.
Regards,
Brett