Re: Partial Reload and syntetic keys - Qlik Community

Report Inappropriate Content · ‎2014-10-28

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.

ali_hijazi · ‎2014-10-28

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

SunilChauhan · ‎2014-10-28

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

Sunil Chauhan

SunilChauhan · ‎2014-10-28

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

Sunil Chauhan

Report Inappropriate Content · ‎2014-10-28

Hello,

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

SunilChauhan · ‎2014-10-28

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

file Menu ->>>>>>Partial Reload

or share sample qvw

Sunil Chauhan

murozel76 · ‎2015-11-04

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!