topic Re: Incremental Load based on Primary Key without Date field Help!!! in QlikView

Incremental Load based on Primary Key without Date field Help!!!

amit_saini — Fri, 06 Jul 2018 08:27:56 GMT

Hi Folks ,

Can someone help we with incremental script for below EDID4 table having "SEGNUM" as Primary Key , we don't have any date field in this table.

EDID4: // IDoc Data Records from 4.0 onwards

Load

SQL Select MANDT COUNTER DOCNUM HLEVEL SEGNUM SEGNAM PSGNUM DTINT2 SDATA from EDID4

;

STORE * FROM EDID4 INTO D:\qvprod\qvd\SAP\KTX_100\EDID4.QVD;

DROP TABLE EDID4;

Thanks in advance!

Regards,

Re: Incremental Load based on Primary Key without Date field Help!!!

jubarrosor — Fri, 06 Jul 2018 09:32:03 GMT

Hi AS:

Try something like this where vL.Segnum contain the max value of field Segnum loaded in the previous execution:

EDID4:

LOAD

FROM (qvd);

Concatenate(EDID4)

Load

SQL

Select MANDT, COUNTER, DOCNUM, HLEVEL, SEGNUM, SEGNAM, PSGNUM, DTINT2, SDATA from EDID4

WHERE SEGNUM > $(vL.Segnum);

STORE * FROM EDID4 INTO D:\qvprod\qvd\SAP\KTX_100\EDID4.QVD;

DROP TABLE EDID4;

Best Regards,

Juan P. Barroso

Re: Incremental Load based on Primary Key without Date field Help!!!

amit_saini — Fri, 06 Jul 2018 11:21:48 GMT

do i need to define:

vL.Segnum=max(SEGNUM) ???

Thanks,

Re: Incremental Load based on Primary Key without Date field Help!!!

manoj217 — Fri, 06 Jul 2018 12:01:38 GMT

Hi Amit,

No Max function will not work because it is a aggregation function.

If you are working with the primary key and it consists a string means use this below variable.

let max=peek('SEGNUM',-1,'EDID4');

Regards,

Manoj

Re: Incremental Load based on Primary Key without Date field Help!!!

manoj217 — Fri, 06 Jul 2018 12:09:13 GMT

Also use lastvalue(SEGNUM) it will also get the last value of the particular field.

Re: Incremental Load based on Primary Key without Date field Help!!!

amit_saini — Fri, 06 Jul 2018 12:18:09 GMT

ok thanks!

Regards,
AS

Re: Incremental Load based on Primary Key without Date field Help!!!

amit_saini — Tue, 10 Jul 2018 08:30:31 GMT

Hi,

I'm not sure , why I'm getting below error:

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 ">2" is invalid here (due to grammar). contains an invalid character or it is a keyword. (It might be possible to escape it using "!"). Or a space is missing or there is one space too many.)

SQL Select MANDT COUNTER SEGNUM HLEVEL DOCNUM SEGNAM PSGNUM DTINT2 SDATA from EDID4

Where SEGNUM >2

I'm trying something like below:

EDID4:

LOAD MANDT,

COUNTER,

SEGNUM,

HLEVEL,

DOCNUM,

SEGNAM,

PSGNUM,

DTINT2,

SDATA

FROM

(qvd);

Last_Updated_SEGNUM:

Load max(SEGNUM) as MaxSEGNUM

Resident EDID4;

Let vLastUpdatedSEGNUM =peek('MaxSEGNUM',0,'Last_Updated_SEGNUM');

NoConcatenate

Incremental:

Load

SQL Select MANDT COUNTER SEGNUM HLEVEL DOCNUM SEGNAM PSGNUM DTINT2 SDATA from EDID4

Where SEGNUM >$(vLastUpdatedSEGNUM);

Concatenate (Incremental)

LOAD MANDT,

COUNTER,

SEGNUM,

HLEVEL,

DOCNUM,

SEGNAM,

PSGNUM,

DTINT2,

SDATA

Resident EDID4

Where not Exists (SEGNUM);

Inner Join (Incremental)

Load

SEGNUM;

SQL Select MANDT COUNTER SEGNUM HLEVEL DOCNUM SEGNAM PSGNUM DTINT2 SDATA from EDID4

;

STORE * FROM Incremental INTO D:\qvprod\qvd\SAP\KTX_100\Incremental.QVD;

DROP Tables Last_Updated_SEGNUM, EDID4;

Thanks,

Re: Incremental Load based on Primary Key without Date field Help!!!

amit_saini — Tue, 10 Jul 2018 08:37:44 GMT

and SEGNUM is Primary Key.

Thanks,

Re: Incremental Load based on Primary Key without Date field Help!!!

amit_saini — Tue, 10 Jul 2018 15:12:03 GMT

Guys any suggestion???

Regards,

Re: Incremental Load based on Primary Key without Date field Help!!!

neelamsaroha157 — Tue, 10 Jul 2018 15:35:26 GMT

May be you can try not exist function in the where condition.

Check this out -

Incremental load with Primary Key