Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to apply Incremental load for the VBRP table from SAP

Dear All,

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

Ritul

4 Replies
Not applicable
Author

Hi All,

Any one help me out for this.

Thanks.

sanjay006
Creator
Creator

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];

EdgarMM
Contributor III
Contributor III

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.

igorgois_
Partner - Creator
Partner - Creator

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