Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I need you help for Incremental load on SAP KONV table, But the problem is KONV table has no last updated date field or other date field.
So, how can I do incremental load on KONV.
your assistance will be highly appreciated
Thanks,
Neha
Hi Neha,
We are Refering EKKO For KONV Table incremental and its working fine.
Please refer below code:
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];
Regards
Does it contains primary key?
It has composite key on 3 fields-
[Doc. condition no._KNUMV], [G/L Account_SAKN1],[Item_KPOSN]
Thanks for your quick reply.
Yeh I saw that post, as the post not marked as correct i'm not sure if it will work for me or not.
But I'm trying with same.
Thanks.
I think it will work.
Yes, some of the posts not closed by the requested person.
Hi Neha,
We are Refering EKKO For KONV Table incremental and its working fine.
Please refer below code:
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];
Regards
Also see below query.
Using the vbrk table you can filter the incremental table.
SELECT requiredcolumns...
FROM KONV k
JOIN vbrk v
On v.knumv=k.knumv
WHERE v.erzet > @fetcheddatevalue
Thank you Vanraj,
I'm trying with your code but i'm getting an error on -
Let _vend = 'AEDAT <=' & ' ' & Chr(39)& $(vToDate) & chr(39);
what you have set for vToDate variable.
I tried with this but VBAK don't have POSNR field so I don't know if KNUMV will work as a primary key between KNOV and VBAK.