topic Re: date issue in QlikView

date issue

upaliwije — Thu, 10 Nov 2016 09:14:30 GMT

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

Re: date issue

tresB — Thu, 10 Nov 2016 09:19:11 GMT

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

Re: date issue

migueldelval — Thu, 10 Nov 2016 09:20:38 GMT

Hi Upali,

How it´s your Date format?

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

Regards

Miguel del Valle

Re: date issue

upaliwije — Thu, 10 Nov 2016 09:20:57 GMT

data look like given below

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

Re: date issue

upaliwije — Thu, 10 Nov 2016 09:23:06 GMT

Still the result is blank

Re: date issue

migueldelval — Thu, 10 Nov 2016 09:24:56 GMT

Hu UPali,

Could you attach a data set?

Regards

Miguel del Valle

Re: date issue

tresB — Thu, 10 Nov 2016 09:27:39 GMT

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.

Re: date issue

Anonymous — Thu, 10 Nov 2016 09:28:01 GMT

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

Re: date issue

migueldelval — Thu, 10 Nov 2016 09:34:19 GMT

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

Re: date issue

Digvijay_Singh — Thu, 10 Nov 2016 09:37:22 GMT

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.

Re: date issue

upaliwije — Thu, 10 Nov 2016 09:38:16 GMT

Yes, What you say is true. I have created two fields from one filed ? How can I solve this problem pls ?

Re: date issue

tresB — Thu, 10 Nov 2016 09:40:43 GMT

You have to handle this in the modelling. Could you post your table structure containing these dates?

Re: date issue

upaliwije — Thu, 10 Nov 2016 10:07:55 GMT

Here is my sample data

Re: date issue

migueldelval — Thu, 10 Nov 2016 10:52:48 GMT

Hi Upali,

Try this example, please.

You can see your requirement in table (Sheet2), and in script (Main2).

Regards

Miguel del Valle

Re: date issue

upaliwije — Thu, 10 Nov 2016 11:04:48 GMT

Thanks

Is there any way of creating field (C-date-I_date) in the load script. then only I can have following age analysis

LOAD *,

if(C_DATE-I_DATE <=30,DUAL('30 DAYS',1),

if(C_DATE-I_DATE>30 AND if(C_DATE-I_DATE<=60,DUAL('60 DAYS',2)),

if(C_DATE-I_DATE>60 AND if(C_DATE-I_DATE<=90,DUAL('90 DAYS',3)),

if(C_DATE-I_DATE >90,DUAL('>90 DAYS',4)))))AS AGE,

Re: date issue

Thu, 10 Nov 2016 11:47:22 GMT

Try resident all data then differentiate the aging.

Temp:

Load C_DATE,

I_DATE,

C_DATE-I_DATE as DAYS_DIFF

From

DataTable;

DDAYS_DIFF_AGING:

Load *,

if(DAYS_DIFF <=30,DUAL('30 DAYS',1),

if(DAYS_DIFF>30 AND if(DAYS_DIFF<=60,DUAL('60 DAYS',2)),

if(DAYS_DIFF>60 AND if(DAYS_DIFF<=90,DUAL('90 DAYS',3)),

if(DAYS_DIFF>90,DUAL('>90 DAYS',4)))))AS AGE

Resident Temp;

Drop Table Temp;