Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Anybody can help me, how to convert this oracle Script into Qlik ?
select t.app_id, t.date_create1, t.date_create2,
(select count(date_key)
from date_master
where dt between trunc(t.date_create1) and trunc(t.date_create2) and holiday_flg='N' and is_weekday = 1) D_ANALYST,
from Table_ALL t;
Thanks
Darmawan
All:
LOAD app_id,
date(date_create1) as date_create1,
date(date_create2) as date_create2,
date(date_create1+iterno()-1) as DatesAll
FROM Table_ALL .qvd
while date_create1+iterno()-1 <=date_create2;
inner join(All)
LOAD date_key ,
date(dt) as DatesAll
FROM date_master
WHERE holiday_flg='N' and is_weekday = 1;
Final:
noconcatenate
LOAD app_id,
date_create1,
date_create2,
Count(date_key) as D_ANALYST
resident Data
group by app_id,
date_create1,
date_create2;
drop table All;
Note: Make sure that ,date_create1,date_create2 & dt are in number format while loading from QVDS
Try this for a while?
Qualify date_create1, date_create2, app_id;
t:
Load date_create1, date_create2, app_id From Table_Fact;
Left Join (t)
Load t.app_id,
t.date_create1,
t.date_create2,
date_key
FROM Table_All;
Left Join(t)
Final_Fact:
NoConcatenate
Load *, Count(date_key) as D_Analyst, 1 as Flag from date_master where (dt >= TimeStamp(t.date_create1) or dt <= TimeStamp(t.date_create2)) or holiday_flg = 'N' or is_weekday = 1 Group By
t.app_id, t.date_create1, t.date_create2, date_key;
Drop Table t;
In general, it is not necessary to convert the SQL statement to QV, you can execute it in the DBMS (Oracle) using the SQL keyword, and this will often perform better than doing the logic in QV:
Table_ALL:
SQL
select t.app_id, t.date_create1, t.date_create2,
(select count(date_key)
from date_master
where dt between trunc(t.date_create1) and trunc(t.date_create2) and holiday_flg='N' and is_weekday = 1) D_ANALYST,
from Table_ALL t;
Everything from SQL to the closing semi-colon is passed verbatim to Oracle and executed there.
...Unless you have a specific reason for needing to move this out of Oracle.
Hi Jonathan,
Sorry I forgot to tell that I have created qvd for TABLE_ALL and DATE_MASTER.
Now, I need to convert the logic into Qlik.
Thanks
All:
LOAD app_id,
date(date_create1) as date_create1,
date(date_create2) as date_create2,
date(date_create1+iterno()-1) as DatesAll
FROM Table_ALL .qvd
while date_create1+iterno()-1 <=date_create2;
inner join(All)
LOAD date_key ,
date(dt) as DatesAll
FROM date_master
WHERE holiday_flg='N' and is_weekday = 1;
Final:
noconcatenate
LOAD app_id,
date_create1,
date_create2,
Count(date_key) as D_ANALYST
resident Data
group by app_id,
date_create1,
date_create2;
drop table All;
Note: Make sure that ,date_create1,date_create2 & dt are in number format while loading from QVDS
Hi Kushal,
I have tried your script but the result is null.
All:
LOAD
APP_ID,
Date(DATE_CREATE1) as DATE_CREATE1,
Date(DATE_CREATE2) as DATE_CREATE2,
date(DATE_CREATE1+iterno()-1) as DATESALL
FROM [lib://UBKK_Folder/Dashboard_1\Data_Dashboard_Flow_CCOS_Status_All_Dmw.qvd.qvd]
(qvd)
While DATE_CREATE1+IterNo()-1 <= DATE_CREATE2;
inner join(All)
LOAD
DATE_KEY,
Date(DT) as DATESALL
FROM [lib://UBKK_Folder/Dashboard_1\Data_Dashboard_Flow_CCOS_Date_Master_Dmw.qvd.qvd]
(qvd)
WHERE HOLIDAY_FLG='N' and IS_WEEKDAY = 1;
Thanks
DATE_CREATE1,DATE_CREATE2 & DT should be in number format. please check
Hi Kushal,
It works, thank you so much for your help
d4rlie891 no problem, but you should mark my answer as correct instead yours
again you marked your answer as helpful not mine