Re: BSID - BSAD Delta Logic - Page 2 - Qlik Community

settu_periasamy · ‎2018-02-05

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

Peter_Cammaert · ‎2018-02-15

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

settu_periasamy · ‎2018-02-15

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?

ahmed_qlik · ‎2019-07-24

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

EdgarMM · ‎2020-07-18

Hi

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.

tmumaw · ‎2020-07-21

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

EdgarMM · ‎2020-07-21

Hi

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.