topic Calculating Across Tables in QlikView

Calculating Across Tables

Anonymous — Tue, 01 Apr 2014 08:26:27 GMT

I have 2 tables which contain dates. In one of them I am calculating the number of days between the dates, however I want to do this across the the both tables.

Using the attached example I want to count the number of days between Auth_Date and End_Date.

I have tried joining the tables but I don't seem to have much success as I still have to do the calculation in one of the tables.

I am sure this is an easy query, but I am still a bit stuck.

Any suggestions?

Phil

Re: Calculating Across Tables

MK_QSL — Tue, 01 Apr 2014 08:33:46 GMT

Create a straight Table

Dimensions

Job_Ref

Auth_Date

End_Date

Expression

NetWorkDays(Auth_Date,End_Date)

Re: Calculating Across Tables

Anonymous — Tue, 01 Apr 2014 08:45:21 GMT

Thanks, but how do I do it in the load script?

You see the problem I have is that I want to do an interval match on the amount of time that it takes. I can only do this if it is in one table (I think)

Re: Calculating Across Tables

MK_QSL — Tue, 01 Apr 2014 08:48:37 GMT

TEMP:

Load * Inline

[

Job_Ref, Auth_Date

a, 01/04/2014

b, 02/04/2014

c, 03/04/2014

];

Join

Load * Inline

[

Job_Ref, Start_Date, End_Date

a, 01/05/2014, 10/06/2014

b, 02/05/2014, 20/06/2014

c, 03/05/2014, 30/06/2014

];

FINAL:

Load *, NetWorkDays(Auth_Date, End_Date) as TotalDays Resident TEMP;

Drop Table TEMP;

===============

Create a straight Table

Dimensions

Job_Ref

Auth_Date

End_Date

Expression

TotalDays

Re: Calculating Across Tables

Anonymous — Tue, 01 Apr 2014 09:02:14 GMT

PFA