Script logic issue while loading data for the first time

[Former Member]

Hi Experts,

I am trying to load a flat file in BPC for the first time in time period 2016.10.While loading I am selecting the option to run default logic.

Default logic file contains the below code:

*WHEN ACCOUNT

*IS BAS(A)

*REC(EXPRESSION = %VALUE%,ACCOUNT = "X")

*IS BAS(B),BAS(C),BAS(D),BAS(E)

*REC(EXPRESSION = -%VALUE%,ACCOUNT = "X")

*IS BAS(D)

*WHEN FUNC_AREA

*IS LMN

*REC(EXPRESSION = -%VALUE%,ACCOUNT = "X")

*ENDWHEN

*ENDWHEN

//*ENDWHEN

Where A,B,C,X are accounts.

I have not given any scoping.

The results I am getting for ACCOUNT X is incorrect.I found out that some accounts may be getting missed in the calculation.

My question is, since this is the first data load for 2016.10, should it not take all the accounts even if scoping is not specified?

Regards,

Varsha

[former_member186338 · Accepted solution]

Hi Vasha,

Please read the detailed explanation about the scope for default.lgf:

"The results I am getting for ACCOUNT X is incorrect.I found out that some accounts may be getting missed in the calculation." - you have to show evidence of the issue!

Vadim

[bishwajit_das]

Hi Varsha,

What is the difference is logic for ACCOUNT is BAS(D) in line 7 and 13 of your code below.

Line 7 : BAS(D) is not considering the FUNC_AREA, so will calculate for all FUNC_AREA ( including LMN)

Line 13: BAS(D) is considering the FUNC_AREA (LMN) - so repeating the same calculation for FUNC_AREA = LMN here.

1. *WHEN ACCOUNT
2. *IS BAS(A)
3. *REC(EXPRESSION = %VALUE%,ACCOUNT = "X")
6. *IS BAS(B),BAS(C),BAS(D),BAS(E)
7. *REC(EXPRESSION = -%VALUE%,ACCOUNT = "X")
10. *IS BAS(D)
11. *WHEN FUNC_AREA
12. *IS LMN
13. *REC(EXPRESSION = -%VALUE%,ACCOUNT = "X")
14. *ENDWHEN
15. *ENDWHEN

Regards,

Bishwajit

[Former Member]

Hi Bishwajit,

It looks like either Varsha solved her problem or lost interest in it, but I can tell you what is the difference and how it will affect the result.

Line 7 offsets all accounts under D while line 13 offsets only those accounts that have FUNC_ARE = LMN. This will result in records that are under D AND have FUNC_ARE = LMN offset twice, i.e. that opposite sign amount will be doubled.

Regards,

Gersh

[bishwajit_das]

Yes Gersh, that's what I thought would be cause of incorrect data loading - so did put up this question to her.

But seems people put up questions and then loose interest .Anyways thanks to SCN that it still replies.

Regards,

Bishwajit

[Former Member]

Hi Experts,

Apologies for the late response as I had been working on quite a few pressing issues in Production.

@Gersh/Bishwajit, the issue is not with the logic as it is how the calculation needs to be performed.

Elaborating more on the issue,

When I load data using /CPMB/IMPORT data Manager package in TIME 2018.10 using the above script, I get incorrect results as shown in the image1.

However, when I scope the logic as follows, I get correct results as shown in IMAGE 2.

*XDIM_MEMBERSET ACCOUNT = BAS(A),BAS(B),BAS(C),BAS(D),BAS(E)

I have performed a validation check in the report by adding local formula in row no 23 which is the same as the logic defined in the script. So In the 1^st Image, the results of row 22 and 23 do not match whereas in the 2^nd image, they do.

The issue can be resolved using scoping but as per my understanding scoping is not usually redefined in Default.LGF. I am still wondering what could be the issue with the script.

Thanks,

Varsha

[former_member186338]

Hi Varsha,

Try the following:

1. Clear all data for 2016.10 using CLEAR package.

2. Configure Light optimization to remove zero's (search for my post with detailed explanation for chain adjustments).

3. Run Light optimization with zero elimination.

4. Check in RSA1 that you have no records for 2016.10 (if there are still some zero's - run in SE38 something like NULL_ELIM - look on my post)

5. Import data again with default.lgf without scoping.

6. Test results.

Vadim

[former_member186338]

P.S. Look here:

[former_member186338]

P.P.S. But I agree with Gersh/Bishwajit that the script itself is strange

[Former Member]

Hi Varsha,

First of all, do you have all those records shown in your screen shot in the file you load? If you don't, only records loaded by the Package will be used in calculations.

Second, do you have records with ACCOUNT under A or B or C or D etc and FUNC_AREA =LMN in your cube? If you have such records, they are not taken into account without scoping, but in your report they are most probably shown. When you put that scoping, it makes the script to retrieve those records from the cube.

It's not common practice to scope data in DEFAULT.LGF; you can do it, but you have to keep in mind that logic re-scoped your load and develop it accordingly.

Regards,

Gersh

[Former Member]

Hi Gersh,

Yes the accounts under A.B.C and D do have data for Functional area LMN. I can see them in the report but I guess as you said they are not taken into account. Was not aware of this.

Scoping only the accounts resolves my issue.

@ Vadim -

I tried clear and run optimization with zero elimination, but got the same results as before after loading data.

Thanks,

Varsha Nalkur

[former_member186338]

By the way, what is the ACCTYPE of the accounts used in calculation? default.lgf and dm package use different sign conversion...

Vadim

[Former Member]

A,X - INC

B,C,D,E- EXP

Thanks,

Varsha

[former_member186338]

Ups, may be the issue is here? Please read about sing conversion in

You will have different results if you run default.lgf using dm package...

Please explain in details your testing steps!

Vadim

[Former Member]

Hi Vadim,

I went through the article and applied sign conversion as mentioned there to my report.

The final result that should get calculated as per sign conversion is 2600 but the logic through DM package fetches -600.

Please see the attached image.

Varsha

[former_member186338]

I still do not understand what do you want to achieve with this script:

LMN is a children of ALL_FA?

You want to double LMN value in the result?

Vadim

[former_member186338]

Like:

*WHEN ACCOUNT

*IS BAS(A)

  *REC(EXPRESSION = %VALUE%,ACCOUNT = "X")

*IS BAS(B),BAS(C),BAS(E)

  *REC(EXPRESSION = -%VALUE%,ACCOUNT = "X")

*IS BAS(D)

*WHEN FUNC_AREA

*IS LMN

  *REC(EXPRESSION = -2*%VALUE%,ACCOUNT = "X")

*ELSE

  *REC(EXPRESSION = -%VALUE%,ACCOUNT = "X")

*ENDWHEN

*ENDWHEN

[Former Member]

Hi Vadim,

Yes that is how the calculation has been written but regardless of that, not sure why does the result appear as -600 because if we calculate by logic in the script, the result is 2600.

Varsha

[former_member186338]

Try my script - what is the result?

[Former Member]

this gives result as 8900

[former_member186338]

Then there is something wrong with your report, may be you have incorrect page axis scope etc...

You have to test script on the simple combination of base members. Use input schedule to model the case!

Recommendation:

Comment default.lgf

Create test data with minimum base members (using input form).

Run my script in UJKT and check results!

Vadim

[Former Member]

Hi Varsha,

How many records does this script calculate? If it's in hundreds, not in thousands, enable detailed log in SPRO for this Model. It will show you (in UJFS) exactly results of the calculation.

If result is correct then something is wrong with the report; if result is wrong please post loaded records as well as records from the log. If you load thousands of records try a smaller test.

Regards,

Gersh

[former_member186338]

It's always necessary to narrow down the issue

[Former Member]

It looks like the logic will write over "X" value with each *REC statement. It would probably leave you with a varied number depending on the FUNCTION_AREA groupings for each set. Perhaps you could create X1,X2 and X3 accounts respectively to test?

[former_member186338]

"It looks like the logic will write over "X" value with each *REC statement." - incorrect, within one WHEN/ENDWHEN loop all records with the same target will aggregate!

[Former Member]

Hi Varsha,

If you have followed the advice you have already received but are still struggling with default logic, I have another suggestion for you:

As this is a stable rule that (I presume) can be followed each time the data is loaded, you could perform this task in the transformation / conversion process. Firstly, in transformation file you should concatenate the ACCOUNT and FUNC_AREA fields (maybe with an underscore between them). Then in the conversion file you can use the various combinations and the value column to map your data to various target accounts and with the appropriate signage (using formula field). For example:

External                      Internal    Formula

100000_FALMN         X              Value*-1

2*                                X              Value*1

3*                                X              Value*1

This should give you the results you are looking for without having to rely on default logic.

Tom.

[former_member186338]

Sorry, but if you look better on the script you will understand that it's not possible to get the same results using conversion...

[Former Member]

Hi Varsha,

No, system doesn't know if it's first load or not. DEAFULT.LGF always gets only scope from the action made right before it's called - in your case from data load. So, only Accounts that were loaded will be processed.

You can find log of that processing in UJFS and it should show you which *IS got some data and which didn't. If your detail log is ON you can even see what records were written by that script.

Regards,

Gersh

[former_member225135]

Hi Varsha,

Can you remove the quotes which are there in X.

As *REC(EXPRESSION = %VALUE%,ACCOUNT = X)

Regards,

Rahul

[former_member186338]

"quotes" - has no effect in this syntax!

Vadim