Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Partial Reload and syntetic keys

Hello all,

I have a question regarding partial reload.

This is the script:

IF IsPartialReload() THEN

add

PHISTTP:

LOAD

    date(date#(TPDTE,'YYYYMMDD'), 'DD.MM.YYYY')                                 AS %PRODUKTION_DATUM,

    TPPALN &TPPALP                                                                AS KEY_PHISTTP,

    TPBMEN                                                                         AS PHISTTP_MENGE_TEILE,

    TPVSST                                                                         AS PHISTTP_VON_SCHNITTSTELLE,

    TPNSST                                                                         AS PHISTTP_NACH_SCHNITTSTELLE,

    TPAPN                                                                         AS PHISTTP_ARBEITSPAKETNUMMER,

    QVDTIMESTAMP                                                                as PHISTTP_QVDTIMESTAMP,

    if(TPVSST =110 and left(TPNSST, 1) <> 8 and left(TPNSST, 1) <> 9, 1, 0)        AS PHISTTP_FLAG_WE_ABRECHNUNG,

    if(TPVSST =110 and (TPNSST=210 OR TPNSST=112),1,0)                             AS PHISTTP_FLAG_WE_KPI,

    if(TPVSST =210 or TPVSST =112,1,0)                                            AS PHISTTP_FLAG_EINLAGERUNG,

    if((TPVSST =210 or TPVSST =112) OR (TPVSST =110 and (TPNSST=210 OR TPNSST=112)), 1, 0)    as PHISTTP_FLAG_WE,

    if(TPNSST =520, 1, 0)                                                        AS PHISTTP_FLAG_KOMMI,

    if(TPVSST =520, 1, 0)                                                        AS PHISTTP_FLAG_VERP

FROM $(v_Folder)\PHISTTP\PHISTTP_$(v_Yesterday).qvd(qvd)

WHERE TPKONZ = $(v_Konzern)

  AND TPFIRM = $(v_Firma)

  AND TPRCDE = 'OK'

  and TPDTE < $(v_Today);

END IF;

PHISTTP:

LOAD

    date(date#(TPDTE,'YYYYMMDD'), 'DD.MM.YYYY')                                 AS %PRODUKTION_DATUM,

    TPPALN &TPPALP                                                                AS KEY_PHISTTP,

    TPBMEN                                                                         AS PHISTTP_MENGE_TEILE,

    TPVSST                                                                         AS PHISTTP_VON_SCHNITTSTELLE,

    TPNSST                                                                         AS PHISTTP_NACH_SCHNITTSTELLE,

    TPAPN                                                                         AS PHISTTP_ARBEITSPAKETNUMMER,

    QVDTIMESTAMP                                                                as PHISTTP_QVDTIMESTAMP,

    if(TPVSST =110 and left(TPNSST, 1) <> 8 and left(TPNSST, 1) <> 9, 1, 0)        AS PHISTTP_FLAG_WE_ABRECHNUNG,

    if(TPVSST =110 and (TPNSST=210 OR TPNSST=112),1,0)                             AS PHISTTP_FLAG_WE_KPI,

    if(TPVSST =210 or TPVSST =112,1,0)                                            AS PHISTTP_FLAG_EINLAGERUNG,

    if((TPVSST =210 or TPVSST =112) OR (TPVSST =110 and (TPNSST=210 OR TPNSST=112)), 1, 0)    as PHISTTP_FLAG_WE,

    if(TPNSST =520, 1, 0)                                                        AS PHISTTP_FLAG_KOMMI,

    if(TPVSST =520, 1, 0)                                                        AS PHISTTP_FLAG_VERP

FROM $(v_Folder)\PHISTTP\PHISTTP_201*.qvd(qvd)

WHERE TPKONZ = $(v_Konzern)

  AND TPFIRM = $(v_Firma)

  AND TPRCDE = 'OK'

  and TPDTE >= $(v_LoadMinDatum);

/******************************************************************************************

* Eliminierung Redundanzen.

******************************************************************************************/

RIGHT JOIN (PHISTTP)

LOAD

    DISTINCT KEY_PHISTTP,

    MAX(PHISTTP_QVDTIMESTAMP)                                                     AS PHISTTP_QVDTIMESTAMP

    RESIDENT PHISTTP

    GROUP BY KEY_PHISTTP;

  

DROP FIELDS PHISTTP_QVDTIMESTAMP;

and it creates model, that i have attached.

I just want to add, these new rows.

6 Replies
ali_hijazi
Partner - Master
Partner - Master

joins have to be eliminated in the partial reload

i.e. if not is partial reload then whenever you got a join

I can walk on water when it freezes
IamBack
Champion
Champion

we could do partial load in such a way

1>for loading first Time

Tab:

add Load

from path

2>.Loading when table is already load you just want to add more calculation to tab

add drop table Tab; // this will show you in underline red color like error but it will work dont worry avbout

Tab:

add Load

from path

hope this helps

IamBack
Champion
Champion

IF IsPartialReload() THEN

add drop table PHISTTP;// use this if you have this table already in your model

PHISTTP:

add LOAD

    date(date#(TPDTE,'YYYYMMDD'), 'DD.MM.YYYY')                                 AS %PRODUKTION_DATUM,

    TPPALN &TPPALP                                                                AS KEY_PHISTTP,

    TPBMEN                                                                         AS PHISTTP_MENGE_TEILE,

    TPVSST                                                                         AS PHISTTP_VON_SCHNITTSTELLE,

    TPNSST                                                                         AS PHISTTP_NACH_SCHNITTSTELLE,

    TPAPN                                                                         AS PHISTTP_ARBEITSPAKETNUMMER,

    QVDTIMESTAMP                                                                as PHISTTP_QVDTIMESTAMP,

    if(TPVSST =110 and left(TPNSST, 1) <> 8 and left(TPNSST, 1) <> 9, 1, 0)        AS PHISTTP_FLAG_WE_ABRECHNUNG,

    if(TPVSST =110 and (TPNSST=210 OR TPNSST=112),1,0)                             AS PHISTTP_FLAG_WE_KPI,

    if(TPVSST =210 or TPVSST =112,1,0)                                            AS PHISTTP_FLAG_EINLAGERUNG,

    if((TPVSST =210 or TPVSST =112) OR (TPVSST =110 and (TPNSST=210 OR TPNSST=112)), 1, 0)    as PHISTTP_FLAG_WE,

    if(TPNSST =520, 1, 0)                                                        AS PHISTTP_FLAG_KOMMI,

    if(TPVSST =520, 1, 0)                                                        AS PHISTTP_FLAG_VERP

FROM $(v_Folder)\PHISTTP\PHISTTP_$(v_Yesterday).qvd(qvd)

WHERE TPKONZ = $(v_Konzern)

  AND TPFIRM = $(v_Firma)

  AND TPRCDE = 'OK'

  and TPDTE < $(v_Today);

END IF;

PHISTTP:

LOAD

    date(date#(TPDTE,'YYYYMMDD'), 'DD.MM.YYYY')                                 AS %PRODUKTION_DATUM,

    TPPALN &TPPALP                                                                AS KEY_PHISTTP,

    TPBMEN                                                                         AS PHISTTP_MENGE_TEILE,

    TPVSST                                                                         AS PHISTTP_VON_SCHNITTSTELLE,

    TPNSST                                                                         AS PHISTTP_NACH_SCHNITTSTELLE,

    TPAPN                                                                         AS PHISTTP_ARBEITSPAKETNUMMER,

    QVDTIMESTAMP                                                                as PHISTTP_QVDTIMESTAMP,

    if(TPVSST =110 and left(TPNSST, 1) <> 8 and left(TPNSST, 1) <> 9, 1, 0)        AS PHISTTP_FLAG_WE_ABRECHNUNG,

    if(TPVSST =110 and (TPNSST=210 OR TPNSST=112),1,0)                             AS PHISTTP_FLAG_WE_KPI,

    if(TPVSST =210 or TPVSST =112,1,0)                                            AS PHISTTP_FLAG_EINLAGERUNG,

    if((TPVSST =210 or TPVSST =112) OR (TPVSST =110 and (TPNSST=210 OR TPNSST=112)), 1, 0)    as PHISTTP_FLAG_WE,

    if(TPNSST =520, 1, 0)                                                        AS PHISTTP_FLAG_KOMMI,

    if(TPVSST =520, 1, 0)                                                        AS PHISTTP_FLAG_VERP

FROM $(v_Folder)\PHISTTP\PHISTTP_201*.qvd(qvd)

WHERE TPKONZ = $(v_Konzern)

  AND TPFIRM = $(v_Firma)

  AND TPRCDE = 'OK'

  and TPDTE >= $(v_LoadMinDatum);

/******************************************************************************************

* Eliminierung Redundanzen.

******************************************************************************************/

RIGHT JOIN (PHISTTP)

LOAD

    DISTINCT KEY_PHISTTP,

    MAX(PHISTTP_QVDTIMESTAMP)                                                     AS PHISTTP_QVDTIMESTAMP

    RESIDENT PHISTTP

    GROUP BY KEY_PHISTTP;

 

DROP FIELDS PHISTTP_QVDTIMESTAMP;

hope this helps

Not applicable
Author

Hello,

when I add the drop table, it only shows data from partial reload. The data from previous reload is missing.

IamBack
Champion
Champion

you need to do partial reload also CTRL+SHFT+R or

file Menu ->>>>>>Partial Reload

or share sample qvw

murozel76
Contributor III
Contributor III

Add Drop Table command line works fine! Though it seems as if there were a mistake -as you also clearly mentioned- it is ok. Thank you for this!