topic Re: Creating a Calculated date field in Script in QlikView

Creating a Calculated date field in Script

Thu, 15 Jan 2015 16:13:53 GMT

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

Re: Creating a Calculated date field in Script

alexandros17 — Thu, 15 Jan 2015 16:37:04 GMT

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

MarcoWedel — Thu, 15 Jan 2015 16:37:53 GMT

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

Re: Creating a Calculated date field in Script

engishfaque — Thu, 15 Jan 2015 16:40:50 GMT

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'

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

Kind regards,

Ishfaque Ahmed

Re: Creating a Calculated date field in Script

MarcoWedel — Thu, 15 Jan 2015 16:41:43 GMT

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

Re: Creating a Calculated date field in Script

Thu, 15 Jan 2015 16:45:41 GMT

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

NetWorkDays(IncidentDate,Notificationdate)

Cheers

Re: Creating a Calculated date field in Script

Fri, 20 Mar 2015 10:14:52 GMT

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?