Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Highlighted
Not applicable

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
Highlighted

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

Highlighted

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);

Highlighted
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

Highlighted

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

Highlighted
Not applicable

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

NetWorkDays(IncidentDate,Notificationdate)

Cheers

View solution in original post

Highlighted
Not applicable

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?