Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

philmywallet
New Contributor III

Calculating Across Tables

Hi

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

4 Replies

Re: Calculating Across Tables

Create a straight Table

Dimensions

Job_Ref

Auth_Date

End_Date

Expression

NetWorkDays(Auth_Date,End_Date)

philmywallet
New Contributor III

Re: Calculating Across Tables

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

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

gupta_n8
Valued Contributor II

Re: Calculating Across Tables

PFA

Community Browser