Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

Re: Creating a Calculated date field in Script

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

NetWorkDays(IncidentDate,Notificationdate)

Cheers

6 Replies

Re: Creating a Calculated date field in Script

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

Re: Creating a Calculated date field in Script

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

Re: Creating a Calculated date field in Script

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

Re: Creating a Calculated date field in Script

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

Re: Creating a Calculated date field in Script

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

NetWorkDays(IncidentDate,Notificationdate)

Cheers

Not applicable

Re: Creating a Calculated date field in Script

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?