date issue

[upaliwije]

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

[tresesco]

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

[migueldelval]

Hi Upali,

How it´s your Date format?

Try it: (num(C_DATE)-num(I_DATE)) AS NUMDAY

Regards

Miguel del Valle

[upaliwije]

data look like given below

C_DATE	I_DATE
41765
41786
41877
42342
42452
42551
42591
	41670
	41698
	41761
	42282
	42359
	42460
	42479

[upaliwije]

Still the result is blank

[migueldelval]

Hu UPali,

Could you attach a data set?

Regards

Miguel del Valle

[tresesco]

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.

[Anonymous]

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

[migueldelval]

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

[Digvijay_Singh]

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.