Field not found (Incremental Load from SQL DB) - Qlik Community

adidac · ‎2021-08-09

Dear Community,

we have a problem with the incremental load of fields from several tables via SQL connection in combination with qvd´s previously generated during a baseline load.

During the baseline load the "trcode" is found, but during the first incremental load we received following error:

Please help us!

_________________________________________________________________________

baseline Data

__________________________________________________________________________

zTemp:
Load
*
From
[$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/loaderLog.qvd] (qvd)

Where
TaskName = 'Base Line Load';

LET vBaseRecords = NoOfRows('zTemp');

drop table zTemp;

If $(vBaseRecords) = 0 then

LIB CONNECT TO 'PostgreSQL_Local_DEU_LOGiQ_Rostock';

//lieferung---------------------------------------------------------------------------------------------------------------------------

lieferung:
LOAD trcode,
lfcode as ZondaNr,
kontraktcode as KontraktNr,
kdcode as SoldtoNr,
kdname1 as SoldtoName1,
kdname2 as SoldtoName2,
baucode as ShiptpNr,
bauname1 as ShiptoName1,
code1 as LOGiQ_LSNr,
text1 as Kommentar1,
text2 as Kommentar2,
text3 as Kommentar3,
timestamp(chdate) as lichdate,
chid as lichid,
kdname3 as SoldtoName3,
kdname4 as SoldtoName4,
extlfnummer as ExtLSNr,
kdtelefon as SoldtoTelNr,
bauland as ShiptoLand,
retelefon,
validationcode as ZondaPIN,
originalquantities as ExtAnliefermenge,
kdauftragsnr as CustOrdNr;

SELECT *
FROM "sysadm"."lieferung";

Let vBaseRecords = NoOfRows('lieferung');

Call CheckDataMaxDate('lieferung','lichdate');

Call WriteToLogFile('Base Line Load','lieferung',vBaseRecords,'Initial Data Load done');

Store lieferung into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/lieferung.qvd] (qvd);

//lieferungext----------------------------------------------------------------------------------------------------------

lieferungext:
LOAD trcode,
contracttype as KontraktTyp,
custrefno as ExtKdRef,
exactdeliveryquantity;
SELECT *
FROM "sysadm"."lieferungext";

Let vBaseRecords = NoOfRows('lieferungext');

// Call CheckDataMaxDate('lieferungext','chdate');

Call WriteToLogFile('Base Line Load','lieferungext',vBaseRecords,'Initial Data Load done');

Store lieferungext into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/lieferungext.qvd] (qvd);

// position--------------------------------------------------------------------------------------------------------------

position:
Load
trcode,
positionsnr as PosNr,
artikelcode as SAPMaterialID,
artikelbezeichnung as Material_Name,
sollnetto as Soll_Menge,
sollstk as Soll_Stk,
sollpal as Soll_Pal,
istnetto as Lade_Menge,
iststk as ist_Stk,
istpal as Ist_Pal,
mengeneh as MengeUOM,
uom as Einheit,
startdatum,
endedatum,
ladestellencode as FB_Soll,
ladestellenbez as FB_SollName,
chdate as pochdate,
chid as pochid,
text4 as NormName,
sampleanalysisperformedflag,
inttechcode,
positiontype as PosTyp,
wg1scalename as WB1stW,
wg2scalename as WB2ndW,
actualloadingstationcode as FB_Ist,
actualloadingstationname as FB_IstName;

SELECT *
FROM "sysadm"."position" ;

Let vBaseRecords = NoOfRows('position');

Call CheckDataMaxDate('position','pochdate');

Call WriteToLogFile('Base Line Load','position',vBaseRecords,'Initial Data Load done');

Store position into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/position.qvd] (qvd);

// transport----------------------------------------------------------------------------------------------------------

transport:
LOAD werksnummer as WerkNr,
trcode,
trart as TrArt,
erstelldatum,
erstellzeit,
trdatum as PlanLadeDatum,
trkz as TrKZ,
idcode as KartenNr,
fzcode1 as TruckID,
fzkennzeichen1 as TruckPlate,
fzcode2 as TrailerID,
fzkennzeichen2 as TrailerPlate,
frcode as KommSpedID,
wg1b1gewicht as W1,
wg1datum as W1_TS,
wg2b1gewicht as W2,
wg2datum as W2_TS,
eindatum as EntryTS,
ausdatum as ExitTS,
Date(ausdatum) as ExitDate,
text1 as ZondaKommentar,
text2 as MitarbeiterKommentar,
text3 as EditKommentar,
status as Status,
wg1b1lfdnr,
wg2b1lfdnr,
chdate as trchdate,
chid as trchid,
streckenmaxlast as MaxNetto,
spcode as ExecSpedID,
spname1 as ExecSpedName,
tan as EncryptNr,
drivername as FahrerName,
text4,
code5 as ListNr,
text6,
fzfmtkennz1,
dtactuallogin,
dtactualloadstart,
dtactualloadend,
dtactuallogout,
frname1 as KommSpedName,
sptelefon as KommSpedTel,
frland as KommSpedLand,
fahreridcardno as [FührerscheinID],
fahrerpersonalno as PersonalausweisID,
bearbeiter as Bearbeiter,
incoterms as Incoterms,
processindicator as TransportArt,
palletsamount as Rückpaletten,
flag7 as IntTr,
wg1scalename,
wg2scalename;

SELECT *

FROM "sysadm"."transport" ;

Let vBaseRecords = NoOfRows('transport');

Call CheckDataMaxDate('transport','trchdate');

Call WriteToLogFile('Base Line Load','transport',vBaseRecords,'Initial Data Load done');

Store [transport] into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/transport.qvd] (qvd);

//bup_transport-------------------------------------------------------------------------------------------------------------

bup_transport:
LOAD bup_date,
bup_action,
chdate,
werksnummer,
trcode,
trart,
status,
text4 as bup_text4;
SELECT *
FROM "sysadm"."bup_transport";
Let vBaseRecords = NoOfRows('bup_transport');

Call CheckDataMaxDate('bup_transport','chdate');

Call WriteToLogFile('Base Line Load','bup_transport',vBaseRecords,'Initial Data Load done');

Store [bup_transport] into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/bup_transport.qvd] (qvd);

Exit Script;

else

Call WriteToLogFile('Base Line Load','lieferung',0,'No Baseline required');
Call WriteToLogFile('Base Line Load','lieferungext',0,'No Baseline required');
Call WriteToLogFile('Base Line Load','position',0,'No Baseline required');
Call WriteToLogFile('Base Line Load','transport',0,'No Baseline required');
Call WriteToLogFile('Base Line Load','bup_transport',0,'No Baseline required');

endif;

_______________________________________________________________________________________________

incremental load

__________________________________________________________________________________________________

zTemp:
Load
*
From
[$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/loaderLog.qvd] (qvd)

Where
TaskName = 'Base Line Load';

LET vBaseRecords = NoOfRows('zTemp');

drop table zTemp;

If $(vBaseRecords) = 0 then

LIB CONNECT TO 'PostgreSQL_Local_DEU_LOGiQ_Rostock';

//lieferung---------------------------------------------------------------------------------------------------------------------------

lieferung:
LOAD trcode,
lfcode as ZondaNr,
kontraktcode as KontraktNr,
kdcode as SoldtoNr,
kdname1 as SoldtoName1,
kdname2 as SoldtoName2,
baucode as ShiptpNr,
bauname1 as ShiptoName1,
code1 as LOGiQ_LSNr,
text1 as Kommentar1,
text2 as Kommentar2,
text3 as Kommentar3,
timestamp(chdate) as lichdate,
chid as lichid,
kdname3 as SoldtoName3,
kdname4 as SoldtoName4,
extlfnummer as ExtLSNr,
kdtelefon as SoldtoTelNr,
bauland as ShiptoLand,
retelefon,
validationcode as ZondaPIN,
originalquantities as ExtAnliefermenge,
kdauftragsnr as CustOrdNr;

SELECT *
FROM "sysadm"."lieferung";

Let vBaseRecords = NoOfRows('lieferung');

Call CheckDataMaxDate('lieferung','lichdate');

Call WriteToLogFile('Base Line Load','lieferung',vBaseRecords,'Initial Data Load done');

Store lieferung into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/lieferung.qvd] (qvd);

//lieferungext----------------------------------------------------------------------------------------------------------

lieferungext:
LOAD trcode,
contracttype as KontraktTyp,
custrefno as ExtKdRef,
exactdeliveryquantity;
SELECT *
FROM "sysadm"."lieferungext";

Let vBaseRecords = NoOfRows('lieferungext');

// Call CheckDataMaxDate('lieferungext','chdate');

Call WriteToLogFile('Base Line Load','lieferungext',vBaseRecords,'Initial Data Load done');

Store lieferungext into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/lieferungext.qvd] (qvd);

// position--------------------------------------------------------------------------------------------------------------

position:
Load
trcode,
positionsnr as PosNr,
artikelcode as SAPMaterialID,
artikelbezeichnung as Material_Name,
sollnetto as Soll_Menge,
sollstk as Soll_Stk,
sollpal as Soll_Pal,
istnetto as Lade_Menge,
iststk as ist_Stk,
istpal as Ist_Pal,
mengeneh as MengeUOM,
uom as Einheit,
startdatum,
endedatum,
ladestellencode as FB_Soll,
ladestellenbez as FB_SollName,
chdate as pochdate,
chid as pochid,
text4 as NormName,
sampleanalysisperformedflag,
inttechcode,
positiontype as PosTyp,
wg1scalename as WB1stW,
wg2scalename as WB2ndW,
actualloadingstationcode as FB_Ist,
actualloadingstationname as FB_IstName;

SELECT *
FROM "sysadm"."position" ;

Let vBaseRecords = NoOfRows('position');

Call CheckDataMaxDate('position','pochdate');

Call WriteToLogFile('Base Line Load','position',vBaseRecords,'Initial Data Load done');

Store position into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/position.qvd] (qvd);

// transport----------------------------------------------------------------------------------------------------------

transport:
LOAD werksnummer as WerkNr,
trcode,
trart as TrArt,
erstelldatum,
erstellzeit,
trdatum as PlanLadeDatum,
trkz as TrKZ,
idcode as KartenNr,
fzcode1 as TruckID,
fzkennzeichen1 as TruckPlate,
fzcode2 as TrailerID,
fzkennzeichen2 as TrailerPlate,
frcode as KommSpedID,
wg1b1gewicht as W1,
wg1datum as W1_TS,
wg2b1gewicht as W2,
wg2datum as W2_TS,
eindatum as EntryTS,
ausdatum as ExitTS,
Date(ausdatum) as ExitDate,
text1 as ZondaKommentar,
text2 as MitarbeiterKommentar,
text3 as EditKommentar,
status as Status,
wg1b1lfdnr,
wg2b1lfdnr,
chdate as trchdate,
chid as trchid,
streckenmaxlast as MaxNetto,
spcode as ExecSpedID,
spname1 as ExecSpedName,
tan as EncryptNr,
drivername as FahrerName,
text4,
code5 as ListNr,
text6,
fzfmtkennz1,
dtactuallogin,
dtactualloadstart,
dtactualloadend,
dtactuallogout,
frname1 as KommSpedName,
sptelefon as KommSpedTel,
frland as KommSpedLand,
fahreridcardno as [FührerscheinID],
fahrerpersonalno as PersonalausweisID,
bearbeiter as Bearbeiter,
incoterms as Incoterms,
processindicator as TransportArt,
palletsamount as Rückpaletten,
flag7 as IntTr,
wg1scalename,
wg2scalename;

SELECT *

FROM "sysadm"."transport" ;

Let vBaseRecords = NoOfRows('transport');

Call CheckDataMaxDate('transport','trchdate');

Call WriteToLogFile('Base Line Load','transport',vBaseRecords,'Initial Data Load done');

Store [transport] into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/transport.qvd] (qvd);

//bup_transport-------------------------------------------------------------------------------------------------------------

bup_transport:
LOAD bup_date,
bup_action,
chdate,
werksnummer,
trcode,
trart,
status,
text4 as bup_text4;
SELECT *
FROM "sysadm"."bup_transport";
Let vBaseRecords = NoOfRows('bup_transport');

Call CheckDataMaxDate('bup_transport','chdate');

Call WriteToLogFile('Base Line Load','bup_transport',vBaseRecords,'Initial Data Load done');

Store [bup_transport] into [$(vG.$(vL.sourceCont)BasePath)LOGiQ_DB/1.QVD/1.Extract/HRO/bup_transport.qvd] (qvd);

Exit Script;

else

Call WriteToLogFile('Base Line Load','lieferung',0,'No Baseline required');
Call WriteToLogFile('Base Line Load','lieferungext',0,'No Baseline required');
Call WriteToLogFile('Base Line Load','position',0,'No Baseline required');
Call WriteToLogFile('Base Line Load','transport',0,'No Baseline required');
Call WriteToLogFile('Base Line Load','bup_transport',0,'No Baseline required');

endif;