Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
neha_shirsath
Valued Contributor

Incremental on SAP KONV Table

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

1 Solution

Accepted Solutions
Partner
Partner

Re: Incremental on SAP KONV Table

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

18 Replies

Re: Incremental on SAP KONV Table

Does it contains primary key?

Re: Incremental on SAP KONV Table

Hi,

Check this thread.. You will get an idea

INCREMENTAL SAP | Qlik Community

neha_shirsath
Valued Contributor

Re: Incremental on SAP KONV Table

It has composite key on 3 fields-

[Doc. condition no._KNUMV],  [G/L Account_SAKN1],[Item_KPOSN]

Thanks for your quick reply.

neha_shirsath
Valued Contributor

Re: Incremental on SAP KONV Table

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.

Re: Incremental on SAP KONV Table

I think it will work.

Yes, some of the posts not closed by the requested person.

Partner
Partner

Re: Incremental on SAP KONV Table

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

Re: Incremental on SAP KONV Table

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



neha_shirsath
Valued Contributor

Re: Incremental on SAP KONV Table

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.

neha_shirsath
Valued Contributor

Re: Incremental on SAP KONV Table

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.