topic Re: BSID - BSAD Delta Logic in QlikView

BSID - BSAD Delta Logic

settu_periasamy — Mon, 05 Feb 2018 11:10:28 GMT

Hi All,

Could anyone provide the SAP 'BSAD' table incremental load logic?

Implemented the below logic but with one issue. if the Document is moved from BSAD to BSID (Open items), it is still there in BSAD.

BSAD:

LOAD

, BUKRS & GJAHR & BELNR & BUZEI as Documentitem;

SELECT * FROM BSAD WHERE AUGBL IN

( SELECT BELNR FROM BSAD WHERE CPUDT >= '$(vLastChangeDate_num)' );

concatenate (BSAD)

LOAD *
from BSAD.qvd (qvd)
where not exists(Documentitem);

Re: BSID - BSAD Delta Logic

settu_periasamy — Tue, 06 Feb 2018 19:40:17 GMT

Could anyone have a look on this?

Re: BSID - BSAD Delta Logic

sannidhikumar — Wed, 07 Feb 2018 04:38:19 GMT

hi ,

check the below link .

https://www.resultdata.com/three-types-of-qlikview-incremental-loads/

Re: BSID - BSAD Delta Logic

settu_periasamy — Wed, 07 Feb 2018 05:08:09 GMT

Hi Pradeep,

Thanks. I want the 'BSAD' table delta logic from SAP.

The logic which i have implemented is working fine.

But, the issue here, some of the documents (BELNR) moved from BSAD to BSID in SAP. (Reversal Clearing)

In my qvd, it is still there.

BSID - Open Items

BSAD - Cleared Payment Items.

whenever the document is completely cleared, those documents are moved to BSAD. If it is open / partially cleared, it will be available in BSID.

Re: BSID - BSAD Delta Logic

settu_periasamy — Tue, 13 Feb 2018 18:38:34 GMT

Hipcammaert marcus_sommer‌ stalwar1‌

Could you please have a look on this? User not able to provide the proper logic to create the Delta.

If you already implemented, Can you provide the hint.

Re: BSID - BSAD Delta Logic

sunny_talwar — Tue, 13 Feb 2018 18:46:15 GMT

Would you be able to provide some sample data to see how exactly the flow of data is with the events you are pointing out above. It might make it easy to understand with some data to look at

Re: BSID - BSAD Delta Logic

settu_periasamy — Tue, 13 Feb 2018 19:51:15 GMT

Hi Sunny,

Thanks for looking this. It is really difficult for me to explain. I will try.

This is related to 'Account Receivable' application.

We are pulling the BSAD table from SAP using the CPUDT (Entered Date) as mentioned in my opening thread.

BSAD contains Partially Paid documents and Fully closed Documents .

But, some of document are deleted from BSAD table in SAP and moved to BSID table (This table contains only Open Items). For example, The data updated into our QVD on last week based on CPUDT. But, today, document removed it from SAP and moved to BSID table.

In our QVD, those documents are still available and doesn't match with SAP.

The business comparing the data from QV and SAP using the transaction FBL5n.

What i was thinking, Load the full documents (Field; BELNR) from BSAD, and using the where exist condition.

Due to huge data, It is taking around 2 hours.

Attached the sample data with actual fields. I thought, if someone implemented for their application, i can use it.

Re: BSID - BSAD Delta Logic

sunny_talwar — Thu, 15 Feb 2018 12:40:24 GMT

I will check this out and see if I can figure something out... never did it before... but no harm in giving it a shot

Re: BSID - BSAD Delta Logic

sunny_talwar — Thu, 15 Feb 2018 12:45:32 GMT

So, this all doesn't seem to bad... this is just looking to incrementally delete rows based on deletion of record in the database.... what you need is a unique key to determine which rows where deleted. I couldn't find a unique key, but do you have one? If you do... then you might have to just pull the unique key from the database for all the records (should be faster because it will be a single field) and right join into your table... this will drop all the columns which are not in the database anymore.

Re: BSID - BSAD Delta Logic

Peter_Cammaert — Thu, 15 Feb 2018 13:23:40 GMT

Primary key for BSID: AUGBL & AUGDT & BELNR & BUKRS & BUZEI & GJAHR & KUNNR & MANDT & UMSKS & UMSKZ & ZUONR

Primary key for BSAD: AUGBL & AUGDT & BELNR & BUKRS & BUZEI & GJAHR & KUNNR & MANDT & UMSKS & UMSKZ & ZUONR

Indeed, they are identical.

Re: BSID - BSAD Delta Logic

Peter_Cammaert — Thu, 15 Feb 2018 13:27:19 GMT

The primary key isn't that complex if you take into account that each of those tables has about 180 columns.

Re: BSID - BSAD Delta Logic

settu_periasamy — Thu, 15 Feb 2018 14:12:12 GMT

You are right. I did the same. Pulled the key fields from SAP ( as mentioned by pcammaert‌ )

and did the inner join with existing QVD. The problem here is , It took's around 2 hours (Only pulling the data).

We can't do this on daily basis.

As i don't understand the complete business, not able to find the proper logic to do this.

I found the below discussion is SAP forum with same issue. But, It seems to be no proper answer.

How to identify reverse or reset document nos from FI tables?

Re: BSID - BSAD Delta Logic

ahmed_qlik — Wed, 24 Jul 2019 13:39:51 GMT

Dear Settu

I know its a bit old thread but I am facing kind of similar problem.

I am creating AR Ageing report in SAP and providing a variable for date on front end, so that user can see back dated Ageing report. I am not able to write down the logic to remove those invoices which has been cleared in the due period.

Can you give some suggestions if you have faced such kind of issue.

Best regards

Ahmed

Re: BSID - BSAD Delta Logic

EdgarMM — Sat, 18 Jul 2020 07:15:58 GMT

The way I found to solved it, since there is not timestamp into these tables is shown as follow:

BSIDTEMP - This table contains the accumulated data up to the last extraction.
BSIDINCR - Contains the information for new records and possible duplicate records based on the time extraction
BSIDDELE - Copy of BSIDINCR with two Fields only, Join from fields (GJAHR & BELNR & BUZEI & ZUONR)as BSIDkey and 'DELETE' as FlagToDelete
BSADTEMP - This table contains the accumulated data up to the last extraction.
BSADINCR - Contains the information for new records and possible duplicate records based on the time extraction
BSADDELE - Copy of BSADINCR with two Fields only, Join from fields (GJAHR & BELNR & BUZEI & ZUONR)as BSIDkey and 'DELETE' as FlagToDelete
BSADvsBSID - Used to identified cleared documents to be removed from BSID
BSIDvsBSAD - Used to identified re-open documents to removed from BSAD

BSID

1 Create the incremental using field CPUDT (BSIDINCR)
2 Create BSIDDELE (QVD) based on the table BSIDINCR records
3 Using applymap to identify records in BSIDTEMP from BSIDDELE and mark them to be deleted
4 Load BSIDTEMP with records <> 'DELETE'
5 Concatenate records from BSIDINCR into BSIDTEMP.

Example with data
BSIDTEMP
Fiscal Year	Document Number	Line item	Assignment	Entered on
GJAHR	BELNR	BUZEI	ZUONR	CPUDT
2020	5000000063	1	0973216	7/18/2019
2020	5000000064	2	0947241	7/18/2019
2020	5000000065	1	0974159	7/18/2019
2020	100000447	2	0974259	7/18/2019
2020	100100480	3	0974357	7/19/2019
2020	1400000310	4	0974453	7/19/2019

BSIDINCR
Fiscal Year	Document Number	Line item	Assignment	Entered on
GJAHR	BELNR	BUZEI	ZUONR	CPUDT
2020	100100480	3	0974357	7/19/2019
2020	1400000310	4	0974453	7/19/2019
2020	5000000066	1	0974459	7/19/2019
2020	5000000067	3	0974460	7/19/2019
2020	5000000068	2	0974461	7/19/2019

BSIDDELE
BSIDKey	FlagToDelete
202010010048030974357	DELETE
2020140000031040974453	DELETE
2020500000006610974459	DELETE
2020500000006730974460	DELETE
2020500000006820974461	DELETE

APPLYMAP FROM BSIDDELE INTO BSIDTEMP
BSIDTEMP
Fiscal Year	Document Number	Line item	Assignment	Entered on	FlagToDelete
GJAHR	BELNR	BUZEI	ZUONR	CPUDT
2020	5000000063	1	0973216	7/18/2019
2020	5000000064	2	0947241	7/18/2019
2020	5000000065	1	0974159	7/18/2019
2020	100000447	2	0974259	7/18/2019
2020	100100480	3	0974357	7/19/2019	DELETE
2020	1400000310	4	0974453	7/19/2019	DELETE

LOAD GJAHR BELNR BUZEI ZUONR CPUDT from BSIDTEMP Where FlagToDelete <> 'DELETE'
Fiscal Year	Document Number	Line item	Assignment	Entered on
GJAHR	BELNR	BUZEI	ZUONR	CPUDT
2020	5000000063	1	0973216	7/18/2019
2020	5000000064	2	0947241	7/18/2019
2020	5000000065	1	0974159	7/18/2019
2020	100000447	2	0974259	7/18/2019

CONCATENATE BSIDTEMP AND BSIDINCR
Fiscal Year	Document Number	Line item	Assignment	Entered on
GJAHR	BELNR	BUZEI	ZUONR	CPUDT
2020	5000000063	1	0973216	7/18/2019
2020	5000000064	2	0947241	7/18/2019
2020	5000000065	1	0974159	7/18/2019
2020	100000447	2	0974259	7/18/2019
2020	100100480	3	0974357	7/19/2019
2020	1400000310	4	0974453	7/19/2019
2020	5000000066	1	0974459	7/19/2019
2020	5000000067	3	0974460	7/19/2019
2020	5000000068	2	0974461	7/19/2019

For BSAD you have to follow the same process; the diference is

BSADINCR - Contains the information for new cleared records and possible duplicate records based on the time extraction. In my previous example July, 19 2019.

1 Create the incremental using fields CPUDT AND BUDAT (BSADINCR)

Once you have your tables BSIDTEMP and BSADTEMP with the incremental records, you have to identify those records that exists in both tables.

1 Create a QVD with BSADTEMP information, this new QVD will be called BSADvsBSID (Using previous Join from fields (GJAHR & BELNR & BUZEI & ZUONR)as BSADkey and 'DELETE' as FlagToDelete)
2 Applymap BSADvsBSID into BSIDTEMP to identify those records that were already cleared
3 Load BSIDTEMP <> from 'DELETE'; at this point we have the Open Documents
4 Create a QVD with BSIDTEMP information, this new QVD will be called BSIDvsBSAD (Using previous Join from fields (GJAHR & BELNR & BUZEI & ZUONR)as BSIDkey and 'DELETE' as FlagToDelete)
5 Applymap BSIDvsBSAD into BSADTEMP to identify those records that were re-open.
6 Load BSADTEMP <> from 'DELETE'; at this point we have the real cleared documents.

Regards,

Edgar.

Re: BSID - BSAD Delta Logic

tmumaw — Tue, 21 Jul 2020 15:56:23 GMT

I know this thread is pretty old, but I have a question and want to see if anyone can answer it. I have pulled BSID/BSAD and I need to identify only the transactions from BSID that are unapplied. Any ideas? My users want to see anything that as unapplied cash. Thanks

Re: BSID - BSAD Delta Logic

EdgarMM — Tue, 21 Jul 2020 16:44:02 GMT

There is a field in BSID (REBZT - Follow-On Document T) that might help you; if the field is blank there is not any process about the document; but if the field has a value it means that the document has a partial payment.

Regards,

Edgar.