Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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
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;
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
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!
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
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!
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);
When doing a simple join it actually works. Thanks a lot Massimo, this helps me taking a huge step forward in my learning!