topic Re: DateDiff for 2 tables and date Fields in QlikView

DateDiff for 2 tables and date Fields

Fri, 28 Apr 2017 12:14:28 GMT

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

Re: DateDiff for 2 tables and date Fields

vishsaggi — Fri, 28 Apr 2017 16:24:39 GMT

Try like using Straight table chart

Add your dimensions

Expr:

= Aanleg_Ontvangstdatum - Ophef_Ontvangstdatum

Re: DateDiff for 2 tables and date Fields

Fri, 28 Apr 2017 16:44:51 GMT

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

Re: DateDiff for 2 tables and date Fields

vishsaggi — Fri, 28 Apr 2017 16:54:42 GMT

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;

Re: DateDiff for 2 tables and date Fields

Sat, 29 Apr 2017 09:38:38 GMT

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.

Re: DateDiff for 2 tables and date Fields

MindaugasBacius — Tue, 02 May 2017 09:57:18 GMT

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;