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

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
Partner - Specialist III
Partner - Specialist III

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;

View solution in original post

5 Replies
vishsaggi
Champion III
Champion III

Try like using Straight table chart

Add your dimensions

Expr:

= Aanleg_Ontvangstdatum - Ophef_Ontvangstdatum

Not applicable
Author

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
Champion III
Champion III

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
Author

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
Partner - Specialist III
Partner - Specialist III

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;