topic Re: Scripting in Qlik in QlikView

Scripting in Qlik

d4rlie891 — Tue, 08 Aug 2017 07:00:00 GMT

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

Re: Scripting in Qlik

Anil_Babu_Samineni — Tue, 08 Aug 2017 07:48:20 GMT

Try this for a while?

Qualify date_create1, date_create2, app_id;

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;

Re: Scripting in Qlik

jonathandienst — Tue, 08 Aug 2017 07:54:16 GMT

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.

Re: Scripting in Qlik

d4rlie891 — Tue, 08 Aug 2017 08:21:49 GMT

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

Re: Scripting in Qlik

Kushal_Chawda — Tue, 08 Aug 2017 09:24:46 GMT

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

Re: Scripting in Qlik

d4rlie891 — Tue, 08 Aug 2017 10:49:34 GMT

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

Re: Scripting in Qlik

Kushal_Chawda — Tue, 08 Aug 2017 11:12:01 GMT

DATE_CREATE1,DATE_CREATE2 & DT should be in number format. please check

Re: Scripting in Qlik

d4rlie891 — Wed, 09 Aug 2017 09:04:11 GMT

Hi Kushal,

It works, thank you so much for your help

Re: Scripting in Qlik

Kushal_Chawda — Wed, 09 Aug 2017 10:46:09 GMT

d4rlie891‌ no problem, but you should mark my answer as correct instead yours

Re: Scripting in Qlik

Kushal_Chawda — Thu, 10 Aug 2017 10:15:14 GMT

again you marked your answer as helpful not mine