topic Re: Partial Reload and syntetic keys in QlikView

Partial Reload and syntetic keys

Tue, 28 Oct 2014 10:09:02 GMT

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.

Re: Partial Reload and syntetic keys

ali_hijazi — Tue, 28 Oct 2014 10:10:45 GMT

joins have to be eliminated in the partial reload

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

Re: Partial Reload and syntetic keys

SunilChauhan — Tue, 28 Oct 2014 10:14:22 GMT

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

Re: Partial Reload and syntetic keys

SunilChauhan — Tue, 28 Oct 2014 10:16:23 GMT

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

Re: Partial Reload and syntetic keys

Tue, 28 Oct 2014 11:28:42 GMT

Hello,

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

Re: Partial Reload and syntetic keys

SunilChauhan — Wed, 29 Oct 2014 02:35:20 GMT

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

file Menu ->>>>>>Partial Reload

or share sample qvw

Re: Partial Reload and syntetic keys

murozel76 — Wed, 04 Nov 2015 11:46:53 GMT

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!