Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating a Calculated date field in Script

Hi All

This is probably an easy question and i will kick myself for asking but here it goes anyway.

I have two date fields which are being loaded into my script as per below.

LOAD ClaimID as ClaimID2.11,

          IncidentDate as IncidentDate2.11,     

          Notificationdate as Notificationdate2.11,

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

Pretty basic right...

I basically want to add a calculated field into the script which is the Notificationdate - IncidentDate  so i can find the days in-between.

I will then use this calculated field as a slider on dashboard.

Any ideas??

Cheers

Andrew

1 Solution

Accepted Solutions
Not applicable
Author

Thanks all... They all work.... I found my answer as soon as i posted .... typical...

NetWorkDays(IncidentDate,Notificationdate)

Cheers

View solution in original post

6 Replies
alexandros17
Partner - Champion III
Partner - Champion III

a date has its numerical representation, simpy doing num(mydate) you obtain a number so:

LOAD ClaimID as ClaimID2.11,

          IncidentDate as IncidentDate2.11,    

          Notificationdate as Notificationdate2.11,

          Date(Num(IncidentDate )-Num(Notificationdate )) as Delta

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

let me know

MarcoWedel

LOAD ClaimID as ClaimID2.11,

          IncidentDate as IncidentDate2.11,  

          Notificationdate as Notificationdate2.11,

          Interval(Notificationdate - IncidentDate) as Interval

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

engishfaque
Specialist III
Specialist III

Dear Andrew,

Try this load script,

LOAD ClaimID as ClaimID2.11,

          IncidentDate as IncidentDate2.11,   

          Notificationdate as Notificationdate2.11,

          IncidentDate,

   Notificationdate,

   Date(Notificationdate, 'DD/MMM/YY') - Date(IncidentDate, 'DD/MMM/YY') as DaysInBetween

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

Note: You can customize date format as your requirement.

For example

Write 'DD-MM-YYYY' instead of 'DD/MMM/YY'

or

Write 'DD/MM/YYYY' instead of 'DD/MMM/YY'

Kind regards,

Ishfaque Ahmed

MarcoWedel

If it's real date fields (no timestamps, no time part), then

LOAD ClaimID as ClaimID2.11,

          IncidentDate as IncidentDate2.11,  

          Notificationdate as Notificationdate2.11,

          Notificationdate - IncidentDate as daysinbetween

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq)

will be enough

hope this helps

regards

Marco

Not applicable
Author

Thanks all... They all work.... I found my answer as soon as i posted .... typical...

NetWorkDays(IncidentDate,Notificationdate)

Cheers

Not applicable
Author

Hi,

can anyone please tell me how to do this if I have a condition that must be met in another import field, from same table?