Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

upaliwije
Contributor II

date issue

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

15 Replies
MVP
MVP

Re: date issue

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
Valued Contributor

Re: date issue

Hi Upali,

How it´s your Date format?

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

Regards

Miguel del Valle

upaliwije
Contributor II

Re: date issue

data look like given below

C_DATEI_DATE
41765
41786
41877
42342
42452
42551
42591
41670
41698
41761
42282
42359
42460
42479
upaliwije
Contributor II

Re: date issue

Still the result is blank

migueldelval
Valued Contributor

Re: date issue

Hu UPali,

Could you attach a data set?

Regards

Miguel del Valle

MVP
MVP

Re: date issue

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.

payalgosar
Contributor II

Re: date issue

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_DATEC_DATEI_DATE
F1/11/20161/11/2016
G2/11/20162/11/2016
F3/11/20163/11/2016
migueldelval
Valued Contributor

Re: date issue

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
Honored Contributor III

Re: date issue

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.

Community Browser