compare data from columns in different tables

[robertskytt]

Hi all!

I'm new to QlikView, please go easy on me 😃

I'm used to T-SQL and BI and I can easily explain what I need to do but I have such limited knowledge in QlikView scripting that things get confusing. So here's a small example of what I'm trying to do. If you can point me to documenation regarding ETL I'm much grateful.

I'm trying to find a way to compare dates for every row in the two tables to be able to calculate the average number of days of the selection I make later in the application.

In T-SQL I would do it like this:

SELECT

  A.StartDate

  , B.EndDate

  , DATEDIFF(DAY, A.StartDate, B.EndDate) AS NumberOfDays

FROM

  TableA AS A

  INNER JOIN TableB AS B

  ON A.ID = B.ID;

In QlikView I managed this far:

SQL SELECT

       ID

     , StartDate

FROM TableA;

INNER JOIN(TableA)

SQL SELECT

     ID

     , EndDate

FROM

     TableB;

Now I wonder how I can compare the two dates in the different tables. Should i do this in the load script?

[ashfaq_haseeb]

Hi,

Try this

Main:
Load *,Interval(EndDate - StartDate,'D') AS NumberOfDays;
SQL SELECT
       ID
     , StartDate
FROM TableA;

INNER JOIN(TableA)

SQL SELECT
     ID
     , EndDate
FROM
     TableB;

Let me know if that worked.

If not then come up with some sample data.

Regards

ASHFAQ

[Kushal_Chawda · Accepted solution]

Tab1:

Load ID,

        StartDate

From A;

INNER JOIN(Tab1)

load ID,

       EndDate

From B

Final:

load *,

floor(EndDate)-floor(StartDate) as NumberOfDays

Resident Tab1;

drop table Tab1;

[maxgro]

there are many possible ways

1)  one table in Qlik using your transact sql knowledge (jon and datediff in sql)

QlikTable:

SQL

SELECT

   A.ID

   , A.StartDate  , B.EndDate

  , DATEDIFF(DAY, A.StartDate, B.EndDate) AS NumberOfDays

FROM

  TableA AS A

  INNER JOIN TableB AS B

  ON A.ID = B.ID;

2)  two tables in Qlik; in Qlik charts you can use EndDate - StartDate to get the difference or interval as already suggested; add avg for average

QlikTable1:

SQL SELECT

       ID     , StartDate

FROM TableA;

QlikTable2:

SQL SELECT

     ID     , EndDate

FROM     TableB;

3)  one tables in Qlik; difference calculated in load

TmpTable:

SQL SELECT

       ID     , StartDate

FROM TableA;

inner join (TmpTable)

SQL SELECT

     ID     , EndDate

FROM     TableB;

QlikTable1:

load

     *,

     EndDate - StartDate as NumberOfDays // or floor(EndDate) - floor(StartDate)

resident TmpTable;

drop table TmpTable;

for loading data in Qlik

this is the best doc I know

LOAD data into QlikView

[robertskytt]

Hi Ashfaq!

This doesn't work since the second tables content will not be found in the LOAD statement. But, what Kush141084 writes works very well.

Thanks a lot guys!

[ashfaq_haseeb]

You can try something like this

Temp:

SQL SELECT  
       ID  
     , StartDate  
FROM TableA;  

INNER JOIN(TableA)  

SQL SELECT  
     ID  
     , EndDate  
FROM  
     TableB; 
Main:
Load *,Interval(EndDate - StartDate,'D') AS NumberOfDays; 
drop table Temp; 

Regards

ASHFAQ

[robertskytt]

Hi Massimo!

1. This does not seem to work. QlikView will not let me do joins inside the SQL statement.

2. I'm not sure how to use this inside the application. I'm very novice when it comes to QlikView so if you could help me understand some more it would be appreciated. Lets say for example that I would like to have the result of the comparison inside a textbox, how could I accomplish this?

3. This one seems to work just the way I expect in comparison to T-SQL.

Thanks!

[maxgro]

1)  this works on my sql server database

Usually you can use whatever valid sql statement (for the db you're using) in the SQL part (after SQL) of Qlik load

QlikTable:

SQL

SELECT a.id, a.startdate, b.enddate,

DATEDIFF(DAY, a.startdate, b.enddate) AS NumberOfDays

FROM TableA a

inner join  TableB b on (a.id=b.id);

[robertskytt]

When doing a simple join it actually works. Thanks a lot Massimo, this helps me taking a huge step forward in my learning!