topic Re: difference between two date fields in QlikView

difference between two date fields

bharatkishore — Thu, 06 Sep 2018 13:05:33 GMT

Hi I have 2 date fields, Month_Code and start date.

Now i need to get the difference between two dates and the desired one should be in months. I need to do in script

Can you please tell me how to do it.

Thanks,

Bharat

Re: difference between two date fields

vvira1316 — Thu, 06 Sep 2018 13:13:52 GMT

Hi Bharat,

Have you tried interval function, or you can use Num and date functions depending on formats. Any sample data may help to understand your particular situation and come up with a solution.

BR,

Vijay

Re: difference between two date fields

bharatkishore — Thu, 06 Sep 2018 13:21:14 GMT

Hi Vijay,

In my script i have the below one:

load Equipment,

System_Code,

Calendar_Month_Code,

Contract,

Warranty,

Installation,

UnResolved,

[T&M],

[Part Group],

Key_EMS,

Install_Flag ,

Removal_Flag ,

Technical_Start_Date,

Technical_Start_Date - Calendar_Month_Code as Age (while subtracting i need to get output in number months ) (Need to derive this new field)

Please let me know if you need anything more.

image for reference.

Thanks,

Bharat

Re: difference between two date fields

bharatkishore — Thu, 06 Sep 2018 13:27:45 GMT

stalwar1‌.. Sunny Bhai can you please have a look..

Re: difference between two date fields

neelamsaroha157 — Thu, 06 Sep 2018 13:34:50 GMT

This may help you -

Calculating Months difference between two dates

Re: difference between two date fields

bharatkishore — Thu, 06 Sep 2018 13:45:09 GMT

thank you...

Re: difference between two date fields

vvira1316 — Thu, 06 Sep 2018 13:52:40 GMT

datatable:
LOAD * Inline
['Calendar_Month_Code', 'Technical_Start_Date'
'2010-02', '2000-09'];

ProcessedTable:
LOAD
Date#(Right([Calendar_Month_Code],2) & '/01/' & Left([Calendar_Month_Code], 4), 'MM/DD/YYYY') as [Calendar_Month_Code],
Date#(Right([Technical_Start_Date],2) & '/01/' & Left([Technical_Start_Date], 4), 'MM/DD/YYYY') as [Technical_Start_Date],
Num(Date#(Right([Calendar_Month_Code],2) & '/01/' & Left([Calendar_Month_Code], 4), 'MM/DD/YYYY')) as CMCNum,
Num(Date#(Right([Technical_Start_Date],2) & '/01/' & Left([Technical_Start_Date], 4), 'MM/DD/YYYY')) as TSDNum,
Date#(Right([Calendar_Month_Code],2) & '/01/' & Left([Calendar_Month_Code], 4), 'MM/DD/YYYY') -
Date#(Right([Technical_Start_Date],2) & '/01/' & Left([Technical_Start_Date], 4), 'MM/DD/YYYY') as Age
Resident datatable;

DROP Table datatable;