Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

DateDiff for 2 tables and date Fields

Good Afternoon,

I am new to Qlik community and I have problems calculating the number of days between 2 fields in 2 different tables.

I want to know the number of days between Ophef_Ontvangstdatum and Aanleg_Ontvangstdatum. ConnectionUid is the key in the table.

Can someone deliver the sql statement for calculating the number of days between the 2 objects?

LOAD Ophef_Order_nummer,
ConnectionUid,
Ophef_Order_type,
Ophef_Porting_ID,
Ophef_SERVICEID,
Ophef_Ontvangstdatum,
[Ophef_Active operator]
FROM

(
ooxml, embedded labels, table is Ophef);

LOAD Aanleg_Order_nummer,
Aanleg_SERVICEID,
ConnectionUid,
Aanleg_Porting_ID,
Aanleg_Order_type,
Aanleg_Ontvangstdatum,
Aanleg_Active_operator
FROM

(
ooxml, embedded labels, table is [Aanleg KPN]);

1 Solution

Accepted Solutions
MindaugasBacius
Valued Contributor II

Re: DateDiff for 2 tables and date Fields

I am not sure - try this:

tmp:

LOAD Ophef_Order_nummer,
ConnectionUid,
Ophef_Order_type,
Ophef_Porting_ID,
Ophef_SERVICEID,
Ophef_Ontvangstdatum,
[Ophef_Active operator]
FROM

(
ooxml, embedded labels, table is Ophef);


tmp2:
LOAD Aanleg_Order_nummer,
Aanleg_SERVICEID,
ConnectionUid,
Aanleg_Porting_ID,
Aanleg_Order_type,
Aanleg_Ontvangstdatum,
Aanleg_Active_operator
FROM

(
ooxml, embedded labels, table is [Aanleg KPN]);


left join (tmp2)

Load ConnectionUid

     ,Ophef_Ontvangstdatum

resident tmp;


noconcatenate

tmp3:

Load *

     ,Ophef_Ontvangstdatum - Aanleg_Ontvangstdatum as Diff

resident tmp2;


Drop table tmp2;

Drop field Ophef_Ontvangstdatum from tmp3;

5 Replies
vishsaggi
Esteemed Contributor III

Re: DateDiff for 2 tables and date Fields

Try like using Straight table chart

Add your dimensions

Expr:

= Aanleg_Ontvangstdatum - Ophef_Ontvangstdatum

Not applicable

Re: DateDiff for 2 tables and date Fields

But i want to add the object in the script, do you know how to edit the script so the new object is created?

vishsaggi
Esteemed Contributor III

Re: DateDiff for 2 tables and date Fields

Can you share some sample data and what is your expected output ?

May be try this?

Table1:

LOAD Ophef_Order_nummer,
ConnectionUid,
Ophef_Order_type,
Ophef_Porting_ID,
Ophef_SERVICEID,
Ophef_Ontvangstdatum,
[Ophef_Active operator]
FROM

(
ooxml, embedded labels, table is Ophef);
JOIN
LOAD Aanleg_Order_nummer,
Aanleg_SERVICEID,
ConnectionUid,
Aanleg_Porting_ID,
Aanleg_Order_type,
Aanleg_Ontvangstdatum,
Aanleg_Active_operator
FROM

(
ooxml, embedded labels, table is [Aanleg KPN]);


NoCocatenate

Final:

LOAD *,

          Aanleg_Ontvangstdatum - Ophef_Ontvangstdatum AS DateDiff

Resident Table1;


Drop Table Table1;

Not applicable

Re: DateDiff for 2 tables and date Fields

Hello,

Thanks for your help, but it is not yet the solution, because the result has to be 2 tables connected by ConnectionUID.

For example first table contains ConnectionUID 123 with ophef_ontvangstdatum 01-01-2015.

second table also contains ConnectionUID 123 with aanleg_ontvangstdatum 01-02-2015.

Output has to be 1 connected object Connection UID, Separate fields ophef_ontvangstdatum and aanleg_ontvangstdatum and a new field DateDif that has the value 31.

Note: There are also ConnectionUID in table 1 that are not available in Table2 and vice versa.

MindaugasBacius
Valued Contributor II

Re: DateDiff for 2 tables and date Fields

I am not sure - try this:

tmp:

LOAD Ophef_Order_nummer,
ConnectionUid,
Ophef_Order_type,
Ophef_Porting_ID,
Ophef_SERVICEID,
Ophef_Ontvangstdatum,
[Ophef_Active operator]
FROM

(
ooxml, embedded labels, table is Ophef);


tmp2:
LOAD Aanleg_Order_nummer,
Aanleg_SERVICEID,
ConnectionUid,
Aanleg_Porting_ID,
Aanleg_Order_type,
Aanleg_Ontvangstdatum,
Aanleg_Active_operator
FROM

(
ooxml, embedded labels, table is [Aanleg KPN]);


left join (tmp2)

Load ConnectionUid

     ,Ophef_Ontvangstdatum

resident tmp;


noconcatenate

tmp3:

Load *

     ,Ophef_Ontvangstdatum - Aanleg_Ontvangstdatum as Diff

resident tmp2;


Drop table tmp2;

Drop field Ophef_Ontvangstdatum from tmp3;

Community Browser