Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have in my data load script has a date field (T_DATE) . I have modified this in the follwing way
IF(POL_TYPE='F',T_DATE) AS C_DATE,
IF(POL_TYPE<>'F',T_DATE) AS I_DATE
I also added following line to the script in order to calculate the no of days between I_DATE and C_DATE
(C_DATE)-(I_DATE) AS NUMDAY
But NUMDAY is blank in the output table. Have i done any error? How can I do the calculation correctly. Pls help
Perhaps your dates are strings which you might have to parse using date#() or so to get a proper date values that have numeric values at the back. How do the data look into these fields? May be like:
Date#(C_DATE, 'DDMMMYY') - Date#(I_DATE, 'DDMMMYY') as NUMDAY
Now replace th1 date format DDMMMYY to according to your data
Hi Upali,
How it´s your Date format?
Try it: (num(C_DATE)-num(I_DATE)) AS NUMDAY
Regards
Miguel del Valle
data look like given below
C_DATE | I_DATE |
41765 | |
41786 | |
41877 | |
42342 | |
42452 | |
42551 | |
42591 | |
41670 | |
41698 | |
41761 | |
42282 | |
42359 | |
42460 | |
42479 |
Still the result is blank
Hu UPali,
Could you attach a data set?
Regards
Miguel del Valle
It would be blank, because your I_DATEs and C_DATEs are mutually exclusive. I.e. - if one record has I_DATE, it doesn't have C_DATE and the otherwise. This happens perhaps because you are creating these two fields from one field. Hope you understand.
Hi,
From the given query I guess, for a given record both dates (C_DATE & I_DATE) wont be there.
Please find below an example for same.
POL_TPE | T_DATE | C_DATE | I_DATE |
---|---|---|---|
F | 1/11/2016 | 1/11/2016 | |
G | 2/11/2016 | 2/11/2016 | |
F | 3/11/2016 | 3/11/2016 |
Maybe you can can make a tmp table grouping for some field that makes than (C_DATE) & (I_DATE) could be at the same line, and then call the tmp table and put your requirement
Regards
Miguel del valle
For any one record you would have either C_DATE or I_DATE, so not sure how you will get the difference, one of the value will be null so it might be showing the difference as null, can you chk if you are using the right logic, may be I am missing something.