Use of LOOKUP in a REC Expression

[former_member201314]

Hi gurus,

I need your help. Again.

In my script, I am trying to read a value from an application using a LOOKUP Command.

As per coding, the looked up value is provided in the LOOKUPID variable. Here: FR1P0

In my REC statement I want to apply the LOOKUP(FR1P0) within my FACTOR command.

Theoretically everything should be fine and even the UJKT script checker looks promising. However, the value being calculated and 'written back' is 0.

The Database (i.e. the planning models) hold all required values needed to process the calculation:

The transaction record that is to be processed is as follows:

The application that holds the values to be looked up, holds the following record:

Please ref. my code and the UJKT Log below.

What am I missing?

*XDIM_MEMBERSET VWR_KONTO = BAS(K.Elem0001)

*XDIM_MEMBERSET VWR_FUNKTIONSBER = <ALL>

*XDIM_MEMBERSET VWR_KATEGORIE = <ALL>

*XDIM_MEMBERSET TIME = BAS(2018.TOTAL)

*XDIM_MEMBERSET VWR_DATENQUELLE = <ALL>

*XDIM_MEMBERSET VWR_PLANUNGSZYKL = <ALL>

*XDIM_MEMBERSET VWR_BEWEGUNGSART = <ALL>

*XDIM_MEMBERSET VWR_WAEHRUNG = EUR

*LOOKUP VWR_BUCHUNGSREGELN

*DIM FR1P0:VWR_SOURCEACCT = "K.Elem0001"

*DIM FR1P0:VWR_TARGETACCT_S = "K.Elem2005"

*DIM FR1P0:VWR_TARGETACCT_H = "NA"

*DIM FR1P0:VWR_BR_FACTOR_TY = "CALC_FACTOR"

*DIM PR1P0:VWR_KOSTENSTELLE = "NA"

*DIM PR1P0:VWR_KATEGORIE = "NA"

*DIM FR1P0:VWR_ZEIT = "PERIOD_P0"

*DIM FR1P0:MEASURES = "PERIODIC"

*ENDLOOKUP

*WHEN VWR_KONTO

*IS BAS(K.Elem0001)

*REC(FACTOR = LOOKUP(FR1P0),VWR_KONTO = K.Elem2005)

*ENDWHEN

*COMMIT

=================================

LOG:

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2018-08-09 13:58:08

FILE:\ROOT\WEBFOLDERS\ENVIRONMENT_VWR \ADMINAPP\VWR_Finanz\TEST.LGF

USER:DREESBACH

APPSET:ENVIRONMENT_VWR

APPLICATION:VWR_Finanz

[INFO] GET_DIM_LIST(): I_APPL_ID="VWR_Finanz", #dimensions=9

MEASURES,TIME,VWR_BEWEGUNGSART,VWR_DATENQUELLE,VWR_FUNKTIONSBER,VWR_KATEGORIE,VWR_KONTO,VWR_PLANUNGSZYKL,VWR_WAEHRUNG

#dim_memberset=3

VWR_KONTO:70001000,70001001,70001002,70001010,70001011,...33 in total.

TIME:2018.01,2018.02,2018.03,2018.04,2018.05,...12 in total.

VWR_WAEHRUNG:EUR,1 in total.

REC :%value%*(LOOKUP(FR1P0))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 1 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: VWR_BUCHUNGSREGELN

QUERY TIME : 1.00 ms. 1 RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

1 RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 1 RECORDS ARE GENERATED.

DATA TO WRITE BACK:

TIME VWR_BEWEGUNGSART VWR_DATENQUELLE VWR_FUNKTIONSBER VWR_KATEGORIE VWR_KONTO VWR_PLANUNGSZYKL VWR_WAEHRUNG SIGNEDDATA

2018.01 BA.NZ DQ.BASIS FB.NZ VS.00_12 K.Elem2005 PZ.2018 EUR 0.00

1 RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :0.00 ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2018-08-09 13:58:09

[former_member186338 · Accepted solution]

Ups, do you really have 2 different labels in LOOKUP definition?

*DIM FR1P0:VWR_SOURCEACCT = "K.Elem0001"

*DIM FR1P0:VWR_TARGETACCT_S = "K.Elem2005"

*DIM FR1P0:VWR_TARGETACCT_H = "NA"

*DIM FR1P0:VWR_BR_FACTOR_TY = "CALC_FACTOR"

*DIM PR1P0:VWR_KOSTENSTELLE = "NA"

*DIM PR1P0:VWR_KATEGORIE = "NA"

*DIM FR1P0:VWR_ZEIT = "PERIOD_P0"

*DIM FR1P0:MEASURES = "PERIODIC"

[former_member186338 · Accepted solution]

Then you can accept it:)

[former_member186338]

"Does it have a performance effect to run the Lookup within or upfront a WHEN-IS-ENDWHEN clause?"

There is no impact on performance, but in some cases you need to be careful with lookup.

In your case it looks correct, you want to multiply existing values in one model by some coefficient stored in another model. Is it correct?

[former_member201314]

Exactly, that's the purpose...

Thanks again for your help Vadim!

Cheers

Claus

[former_member186338]

In other cases you may need to copy values from one model to another. It's not recommended to use LOOKUP for this purpose (pull records). The correct way is to use push with DESTINATION_APP

[former_member201314]

Ah... interesting! I'll keep that in mind and will familiarize myself with this command...

Cheers

Claus

[former_member186338]

Standard approach to copy data from one model to another:

1. Main script is launched in the target model.

2. Using REC(EXPRESSION=0) the data in the required scope is cleared.

3. RUNLOGIC_PH badi is used to launch script in source model

4. Script in source model use *DESTINATION_APP WHEN/ENDWHEN to push data.

If you want to launch main script in the source model - then clear script has to be launched using RUNLOGIC_PH

[former_member186338]

Please, don't use RSA1 to show records!

Use BPC report with all dimensions visible!

[former_member186338]

P.S. You can also use test script in UJKT like:

//For VWR_Finanz model
*XDIM_MEMBERSET VWR_KONTO = BAS(K.Elem0001)
*XDIM_MEMBERSET VWR_FUNKTIONSBER = <ALL>
*XDIM_MEMBERSET VWR_KATEGORIE = <ALL>
*XDIM_MEMBERSET TIME = BAS(2018.TOTAL)
*XDIM_MEMBERSET VWR_DATENQUELLE = <ALL>
*XDIM_MEMBERSET VWR_PLANUNGSZYKL = <ALL>
*XDIM_MEMBERSET VWR_BEWEGUNGSART = <ALL>
*XDIM_MEMBERSET VWR_WAEHRUNG = EUR

*WHEN VWR_KONTO
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN

[former_member186338]

And:

//For VWR_BUCHUNGSREGELN
*XDIM_MEMBERSET VWR_SOURCEACCT = K.Elem0001
*XDIM_MEMBERSET VWR_TARGETACCT_S = K.Elem2005
*XDIM_MEMBERSET VWR_TARGETACCT_H = NA
*XDIM_MEMBERSET VWR_BR_FACTOR_TY = CALC_FACTOR
*XDIM_MEMBERSET VWR_KOSTENSTELLE = NA
*XDIM_MEMBERSET VWR_KATEGORIE = NA
*XDIM_MEMBERSET VWR_ZEIT = PERIOD_P0
*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN VWR_SOURCEACCT
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN

[former_member201314]

Thanks a lot for you input, Vadim.

And I found the error: Due to copy and paste I managed to thoroughly a typo in the LOOKUPID variables.

That of cause didn't like the system, retrieving looked up values = 0.

BTW, Vadim:

Does it have a performance effect to run the Lookup within or upfront a WHEN-IS-ENDWHEN clause?

What do you recommend?

Cheers

Claus

[former_member186338]

If you look on my other answer you can see that I also found typo 🙂