Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
joins have to be eliminated in the partial reload
i.e. if not is partial reload then whenever you got a join
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
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
Hello,
when I add the drop table, it only shows data from partial reload. The data from previous reload is missing.
you need to do partial reload also CTRL+SHFT+R or
file Menu ->>>>>>Partial Reload
or share sample qvw
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!