topic Re: How to apply Incremental load for the VBRP table from SAP in QlikView

How to apply Incremental load for the VBRP table from SAP

Thu, 30 May 2013 07:38:27 GMT

Dear All,

How can i incrementally load the table VBRP from sap into qlikview.

Ritul

Re: How to apply Incremental load for the VBRP table from SAP

Fri, 21 Jun 2013 05:56:34 GMT

Hi All,

Any one help me out for this.

Thanks.

Re: How to apply Incremental load for the VBRP table from SAP

sanjay006 — Thu, 11 May 2017 11:44:16 GMT

Hi Raj !

Plz refer the below Script

We are Refering EKKO For KONV Table incremental and its working fine.

Let vStartTime=num(Now());
/**************** Maxdate Start **************/
Temp_date:
LOAD [Created on_AEDAT] as Date
FROM [$(vTransaction_Qvd)\EKKO.QVD] (qvd);

Max_Table_date:
LOAD Max(Date) as MaxDate;

LOAD FieldValue('Date',IterNo()) as Date
AutoGenerate(1)
While not IsNull(FieldValue('Date',IterNo()));

LET vDateMax = date(num(Peek('MaxDate',0,'Max_Table_date')-2),'YYYYMMDD');

DROP Table Temp_date;
DROP Table Max_Table_date;

/**************** Maximum Date Ends *************/
Let _vstart = 'Where AEDAT >=' & ' ' & Chr(39)& $(vDateMax) & chr(39);
Let _vend = 'AEDAT <=' & ' ' & Chr(39)& $(vToDate) & chr(39);

[KONV]: // Conditions (Transaction Data)
Load
[KBETR] as [Amount_KBETR],
[WAERS] as [Currency_WAERS],
[KSCHL] as [Condition type_KSCHL],
[KNUMV] as [Doc. condition_KNUMV],
[ZAEHK] as [Counter_ZAEHK],
[KPOSN] as [Item_KPOSN],
[STUNR] as [Step number_STUNR],
[KWERT] as [Condition value_KWERT],
[KAWRT] as [Condition.base value_KAWRT],
[KPEIN] as [Pricing unit_KPEIN],
[KKURS] as [Cond.exch.rate_KKURS],
[KMEIN] as [Condition unit_KMEIN] ,
[KNUMV] & [KPOSN] & [STUNR] & [ZAEHK] as %Documentconditionkey;
SQL SUBSELECT KBETR WAERS KSCHL KNUMV KPOSN STUNR ZAEHK KWERT KAWRT KPEIN KKURS KMEIN from KONV
Where KNUMV In (Select KNUMV from EKKO $(_vstart) and $(_vend));

Concatenate

Load *,
[Doc. condition_KNUMV] & [Item_KPOSN] & [Step number_STUNR] & [Counter_ZAEHK] as %Documentconditionkey
From [$(vTransaction_Qvd)\KONV.QVD](qvd)
Where Not Exists(%Documentconditionkey,[Doc. condition_KNUMV] & [Item_KPOSN] & [Step number_STUNR] & [Counter_ZAEHK]);

DROP Field %Documentconditionkey;

STORE * FROM [KONV] INTO $(vTransaction_Qvd)\KONV.QVD;
DROP Table [KONV];

Re: How to apply Incremental load for the VBRP table from SAP

EdgarMM — Mon, 09 Jul 2018 15:33:28 GMT

Sanjay

I'm pretty new in this Qlikview world, and I would like to know if your script also update deleted records too, I mean, for example if a record is deleted in my SAP database, this script willl detecte this deleted record and will be updated in my current QVD? I'm looking for a script to insert, update and delete records; I'm using the SAP Connector to do this incremental; till now I'm able to do this with headers tables (VBAK, VBRK, VBUK, LIKP) succesfully but I'm having the issue with details tables (VBAP, VBRP, VBUP and LIPS). I already read a lot of post related to this but no one of those explain how to apply this for details tables.

This is the script I'm following to create my incrementals:

Let ThisExecTime = ReloadTime();

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then

STORE QV_Table INTO File.QVD;

Let LastExecTime = ThisExecTime;

End If

The issue here is that the PrimaryKey in details tables is based in two fields and I don not find the way to create this primaryKey since after the Inner Join I´m comparing the records between my Inremental file, my QVD agains the SAP table.

This is my script till now:

VBELN is the document number

POSNR is the position number

This two fields are my PrimaryKey

...

Inner Join SQL SELECT VBELN POSNR FROM LIPS
WHERE VBELN IN
(
SELECT VBELN FROM LIKP WHERE
(
(ERDAT > $(vFrom))
AND
$(vDoctosLIKP)
AND EXPKZ <> 'X'
));

If ScriptErrorCount = 0 then
STORE LIPSINCR into [..\QVD\LIPSTEMP.QVD] (qvd);
End If

DROP TABLE LIPSINCR;

Best regards,

Edgar.

Re: How to apply Incremental load for the VBRP table from SAP

igorgois_ — Sat, 11 Aug 2018 01:56:37 GMT

hello sanjya,

We are doing exatc the same subselect with konv and we are getting this error:

2018-08-11 00:00:15 0015 KONV:

2018-08-11 00:00:15 0016 Load *

2018-08-11 00:00:15 0017 SQL

2018-08-11 00:00:15 0018 SUBSELECT

2018-08-11 00:00:15 0019 MANDT,

2018-08-11 00:00:15 0020 KNUMV,

2018-08-11 00:00:15 0021 KPOSN,

2018-08-11 00:00:15 0022 STUNR,

2018-08-11 00:00:15 0023 ZAEHK,

2018-08-11 00:00:15 0024 KDATU,

2018-08-11 00:00:15 0025 KSCHL,

2018-08-11 00:00:15 0026 KPEIN,

2018-08-11 00:00:15 0027 KBETR

2018-08-11 00:00:15 0028 FROM KONV

2018-08-11 00:00:15 0029 WHERE KNUMV IN (

2018-08-11 00:00:15 0030 SELECT

2018-08-11 00:00:15 0031 KNUMV

2018-08-11 00:00:15 0032 FROM EKKO

2018-08-11 00:00:15 0033 WHERE AEDAT >= '20170101'

2018-08-11 00:00:15 0034 AND AEDAT < '20180101'

2018-08-11 00:00:15 0035 )

2018-08-11 00:20:27 9 fields found: MANDT, KNUMV, KPOSN, STUNR, ZAEHK, KDATU, KSCHL, KPEIN, KBETR,

2018-08-11 00:20:27 Error: QVX_UNEXPECTED_END_OF_DATA: TIMEOUT_READ_MEMORY

2018-08-11 00:20:27 Execution Failed

2018-08-11 00:20:27 Execution finished.

The dba says this table doesnt exist in database. That it might be a SAP object

Can you help me?

Thanks in advance