Scripting in Qlik

[d4rlie891]

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

[Anil_Babu_Samineni]

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;

[jonathandienst]

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]

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

[Kushal_Chawda · Accepted solution]

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]

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

[Kushal_Chawda]

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

[d4rlie891]

Hi Kushal,

It works, thank you so much for your help

[Kushal_Chawda]

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

[Kushal_Chawda]

again you marked your answer as helpful not mine