topic Re: compare data from columns in different tables in QlikView

compare data from columns in different tables

robertskytt — Sun, 26 Oct 2014 11:11:05 GMT

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

, StartDate

FROM TableA;

INNER JOIN(TableA)

SQL SELECT

, 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?

Re: compare data from columns in different tables

ashfaq_haseeb — Sun, 26 Oct 2014 11:24:49 GMT

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

Re: compare data from columns in different tables

Kushal_Chawda — Sun, 26 Oct 2014 11:40:17 GMT

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;

Re: compare data from columns in different tables

maxgro — Sun, 26 Oct 2014 11:46:49 GMT

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

Re: compare data from columns in different tables

robertskytt — Sun, 26 Oct 2014 12:11:53 GMT

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!

Re: Re: compare data from columns in different tables

ashfaq_haseeb — Sun, 26 Oct 2014 12:16:32 GMT

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

Re: compare data from columns in different tables

robertskytt — Sun, 26 Oct 2014 12:16:43 GMT

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!

Re: compare data from columns in different tables

maxgro — Mon, 27 Oct 2014 08:49:06 GMT

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

Re: compare data from columns in different tables

robertskytt — Mon, 27 Oct 2014 09:47:39 GMT

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