Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

d4rlie891
New Contributor III

Scripting in Qlik

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

1 Solution

Accepted Solutions

Re: Scripting in Qlik

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

9 Replies

Re: Scripting in Qlik

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;

MVP
MVP

Re: Scripting in Qlik

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.

d4rlie891
New Contributor III

Re: Scripting in Qlik

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

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

d4rlie891
New Contributor III

Re: Scripting in Qlik

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

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

d4rlie891
New Contributor III

Re: Scripting in Qlik

Hi Kushal,

It works, thank you so much for your help

Re: Scripting in Qlik

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

Re: Scripting in Qlik

again you marked your answer as helpful not mine