one Currency to Multiple Currency conversion while Saving budget data

[Former Member]

Hi

I need help on currency conversion

my requirement is Users enter Budget data based on selections ENTITY, and "LC(Local currency).

If they enter data in LC then corresponding ENTITY:CURRENCY is taken as Local currency and in Exchange Rate table corresponding exchange rate values need to convert.

Ex:

IN ENTITY DIMENSION

ENTITY : 2200, Currency: SGD

In Rate Model

Category    inputcurrcy     rateentity     rateid     signdata

BUDGET       SGD                CNY          BUD        0.777

BUDGET       SGD                 EUR         BUD        1.7575

BUDGET       SGD                 GLOBAL   BUD       0.656

now user enters budget data in SGD then SGD value need to convert into CNY, EUR and GLOBAL.

this can be dynamic but not hardcoded

I wrote script logic, but it is not working while saving data, my input schedule script logic is in "DEFAULT.LGF"

I hardcoded for GLOBAL then it is working fine, now I want all currencies then script logic is not working

*SELECT(%RT%,"[ID]",INPUTCURRENCY, "[REPORTING]='Y'")

*LOOKUP RATE-BUD

*DIM CATEGORY=BUDGET

*DIM RATEID=BUD

*DIM SOURCECURR:INPUTCURRENCY=ENTITY.CURRENCY

//*DIM TIME=%TIME

*FOR %CUR%=%RT%

*DIM %CUR%:RATEENTITY="%CUR%"

*NEXT

*ENDLOOKUP

*WHEN CURRENCY

*IS LC

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*FOR %LOOP_CUR%=%RT%

     *REC(EXPRESSION=%VALUE%

     *LOOKUP(%CUR%),CURRENCY=%CUR%)

*NEXT

*ENDWHEN

*ENDWHEN

*ENDWHEN

I tried for BADI also but couldn't get, can anyone please help me in this regard for multiple currency conversion from one Local Currency

Regards

Silu

[former_member200327]

Hi Silu,

Can you please explain what does it mean "script is not working"?

There are number of strange things in your script:

1. Why do you need SOURCECURR in your LOOKUP if you don't refer to it anywhere? I'd think that line will be ignored when LOOKUP is called.

2. If you have *FOR %LOOP_CUR%=%RT% then LOOP_CUR should be used in *REC, not CUR.

Regards,

Gersh

[Former Member]

Hi Gersh

Thank you, I corrected my code and executed it, but records are not converting based on exchange rates, the exchange rate values are not extracting into code, i debug the code in UJKT, please check below code and ujkt validation

UJKT says records generated, but currency conversion not happening, the same value what i entered in LC stored in all currencies without exchange rate calculation. *LOOKUP(LOOKRATE) is not getting value.

Script Logic

*SELECT(%RT%,"[ID]",INPUTCURRENCY, "[REPORTING]='Y'")

*LOOKUP RATE-BUD

*DIM CATEGORY=BUDGET

*DIM RATEID=BUD

*DIM INPUTCURRENCY=ENTITY.CURRENCY

//*DIM TIME=%TIME

*FOR %CUR%=%RT%

*DIM LOOKRATE:RATEENTITY=%CUR%

*NEXT

*ENDLOOKUP

*WHEN CURRENCY

*IS LC

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*FOR %CUR%=%RT%

*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=%CUR%)

*NEXT

*ENDWHEN //CLOSE CATEOGRY

*ENDWHEN  // CLOSE CURRENCY

*ENDWHEN

UJKT Execution:

LGX:

*LOOKUP RATE-BUD

*DIM CATEGORY=BUDGET

*DIM RATEID=BUD

*DIM INPUTCURRENCY=ENTITY.CURRENCY

*DIM LOOKRATE:RATEENTITY=CNY

*DIM LOOKRATE:RATEENTITY=EUR

*DIM LOOKRATE:RATEENTITY=JPY

*DIM LOOKRATE:RATEENTITY=SGD

*ENDLOOKUP

*WHEN CURRENCY

*IS LC

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=CNY)

*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=EUR)

*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=JPY)

*REC(EXPRESSION=%VALUE%*LOOKUP(LOOKRATE),CURRENCY=SGD)

*ENDWHEN

*ENDWHEN

*ENDWHEN

LOG:

FILE:\ROOT\WEBFOLDERS\BIOSENSORS \ADMINAPP\SALESPLAN\TEST.LGF

USER:V-SREEVANI

APPSET:BIOSENSORS

APPLICATION:SALESPLAN

[INFO] GET_DIM_LIST(): I_APPL_ID="SALESPLAN", #dimensions=17

ACCOUNT,CATEGORY,COUNTRY,CURRENCY,DC,ENTITY,MATTYPE,MEASURES,PRODH1,PRODH2,PRODH3,PRODH4,REVENUE,SALESMATRIX,SALESREP,SEGMENT,TIME

#dim_memberset=0

REC :%VALUE%*LOOKUP(LOOKRATE)

REC :%VALUE%*LOOKUP(LOOKRATE)

REC :%VALUE%*LOOKUP(LOOKRATE)

REC :%VALUE%*LOOKUP(LOOKRATE)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 2.00 ms. 6  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: RATE-BUD

QUERY TIME : 0.00 ms. 130  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

24  RECORDS ARE GENERATED.

CALCULATION END.

SCRIPT RUNNING TIME IN TOTAL:2.00 s.

Regards

Silu

[Former Member]

Hi

Forgot to attach the data file and forgot to mention my ENTITY.CURRENCY is SGD

1. Application Data

2. Exchange Rate values

Regards

Silu

[former_member200327]

Hi Silu,

You fixed LOOKUP in one place, but made it worse in another - your FOR/NEXT loop inside LOOKUP was correct in the your original version.

And in REC in LOOKUP you have to use %CUR%, not LOOKRATE.

ID you use to call LOOKUP should match ID you have in LOOKUP.

Regards,

Gersh

[Former Member]

Hi

Thank you, when i use %CUR% in LOOKUP

*REC(EXPRESSION=%VALUE%*LOOKUP(%CUR%),CURRENCY=%CUR%) while validate i recieved below error

UJK_VALIDATION_EXCEPTION:LINE 22 syntax error: " ReferenceError: LOOKUP is not defined"

Can you please send me complete correct code

Regards

Silu

[Former Member]

Hi

anyone can help me in below script logic for currency conversion

User enter budget data for any currency and Saves data, the value need to convert into local currency based on ENTITY selected in the Template, below is script logic

Script logic working fine without error, but data is not converting.

I entered 102 value and saved data, the same value posted to different currencies without conversion, is my script logic wrong. please help me in this regard

*SELECT(%CUR%,"[CURRENCY]",ENTITY, ID=%ENTITY_SET%)

*SELECT(%RT%,"[ID]",INPUTCURRENCY, "[REPORTING]='Y'")

*LOOKUP RATE-BUD

*DIM CATEGORY="BUDGET"

*DIM RATEID="BUD"

*DIM RATEENTITY=%CUR%   (this is Local currency based on ENTITY)

*DIM MEASURES="PERIODIC"

*FOR %LOOP_CUR%=%RT%

*DIM LOOKRATE:INPUTCURRENCY="%LOOP_CUR%"   (this is currency selected by users to enter value)

*NEXT

*ENDLOOKUP

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*FOR %LOOP_CUR%=%RT%

*REC(EXPRESSION=%VALUE%/LOOKUP(LOOKRATE), CURRENCY=%LOOP_CUR%)

*NEXT

*ENDWHEN

*ENDWHEN

Regards

Silu

[former_member186338]

Hi Silu,

I recommend you to read help about LOOKUP, FOR/NEXT etc and reproduce samples in UJKT. You are writing strange things in your code.

Vadim

[Former Member]

Hi Vadim

Thank you, actually i checked the code in Premier Script logic pdf document and tested, but still it is not working, can you please help me in this regard

Regards

Silu

[former_member186338]

Use only help and test in UJKT. Even help has a lot of errors, other documents are worse...

UJKT test will show you the result of FOR/NEXT translation etc.

Vadim

[former_member200327]

Hi Silu,

You got that error message because you made changes only to one part of the code and not to another. Please read carefully what I told you to do.

The part you missed is to replace LOOKRATE in LOOKUP with what you had initially

*FOR %CUR%=%RT%

*DIM %CUR%:RATEENTITY="%CUR%"

*NEXT

As I tried explaining to you ID in LOOKUP and parameter with which you call LOOKUP must be in sync. When you change only one part they go out of sync and you getting a syntax error.

Regards,

Gersh

[Former Member]

Hi Gersh

Thank you, i did as per your suggestion in LOOKUP

but when i post record to currency in *REC statement, i'm getting error, is below statement correct or not

*FOR %LCUR%=%RT%

*REC(EXPRESSION=%VALUE%/LOOKUP(%LCUR%), CURRENCY=%LOOP_CUR%)

*NEXT

I'm receiving error as

VALIDATION_EXCEPTION:LINE 19 syntax error: " ReferenceError: LOOKUP is not defined"

I'm not familiar with script logic, please anyone help me in this regard

Regards

Silu (Sailaja)

[former_member200327]

Hi Silu,

Can you please post the whole script?

The way you posted I'd expect script to complain about %LOOP_CUR% because I don't where it's defined.

Regards,

Gersh

[former_member186338]

And looks like the topic author don't want to use UJKT and look on UJKT results...

Vadim

[former_member200327]

He did this, but with wrong script. So, it was worthless.

Gersh

[former_member186338]

Ups, I missed the post with UJKT results!

He don't understand the idea of LOOKUP:

1. LOOKUP label is defined for a single member or for the member derived from the property of the current member.

2. If you have to get value from multiple members - you have to use multiple labels.

3. Member ID can be used as a LOOKUP label or a part of LOOKUP label.

4. FOR/NEXT is used to create multiple LOOKUP labels.

5. In REC the LOOKUP is used with a required label - in case of multiple labels FOR/NEXT is used with REC.

6. Not sure but in case of multiple FOR/NEXT loops the loop variable have to be named differently.

The code after FOR/NEXT processing can be viewed as LGX in UJKT.

Vadim

[Former Member]

Hi Gersh

Thank you for helping me, please find below script logic and do the needful.

If I use LOOKUP( %CUR%) in *REC then I'm getting error "Reference Error LOOKUP is not defined" ,

If I use just LOOKUP(CUR) in *REC I'm getting error as "LOOKUP ID CUR not found".

*SELECT(%LCUR%,"[CURRENCY]",ENTITY, ID=%ENTITY_SET%)

*SELECT(%RT%,"[ID]",INPUTCURRENCY, "[REPORTING]='Y'")

*LOOKUP RATE-BUD

*DIM CATEGORY="BUDGET"

*DIM RATEID="BUD"

*DIM RATEENTITY=%LCUR%

*DIM MEASURES="PERIODIC"

*FOR %CUR%=%RT%

*DIM %CUR%:RATEENTITY="%CUR%"

*NEXT

*ENDLOOKUP

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*FOR %CUR%=%RT%

*REC(EXPRESSION=%VALUE%/LOOKUP(%CUR%), CURRENCY=%CUR%)

*NEXT

*ENDWHEN

*ENDWHEN

Thank you

Regards

Silu

[former_member200327]

Hi Silu,

Why do you have RATEENTITY twice in LOOKUP: once without a label and once with a label?

You need only line with label. Please remove line *DIM RATEENTITY=%LCUR%

Regards,

Gersh

[Former Member]

Oh, sorry it should be INPUTCURRENCY

*DIM %CUR%:INPUTCURRENCY=%CUR%

I corrected it

can I know what should be in *REC

Regards

Silu

[former_member200327]

Same thing as I said earlier

*FOR %CUR%=%RT%

*REC(EXPRESSION=%VALUE%/LOOKUP(%CUR%), CURRENCY=%CUR%)

*NEXT

Gersh

[Former Member]

Hi

I tried this but receiving error, please find attached screen shot

REgards

Silu

[former_member186338]

You use variables coming from user input - validation will generate error! Please test the same in UJKT with correct scope parameters.

Vadim

*SELECT(%LCUR%,"[CURRENCY]",ENTITY, ID=%ENTITY_SET%)

Result - %LCUR% is empty during validation

[Former Member]

Hi

Can anyone help me on currency conversion script logic.

UJKT I'm able to get all result.

but exchange rate values are not appearing, find the screen shot and script logic above in this forum,

REC statement giving error

Regards

Silu

[former_member186338]

In your post with UJKT results you use incorrect lookup!

Test the latest code in UJKT and post the results!

Vadim

[Former Member]

Hi Gersh

can you help me, this I need to fix asap.

Hi Vadim

you are not understanding my point, I gave my ENTITY code as E2100 in Select statement and did UJKT, if you can see above forum, you can get the details

I sent one screen shot that in *REC i'm getting error by giving LOOKUP(%CUR%)

*FOR %CUR%=%RT%

*REC(EXPRESSION=%VALUE%/LOOKUP(%CUR%), CURRENCY=%CUR%)

*NEXT

Regards

Silu

[former_member186338]

FULL UJKT results!

Original script

Scope (Data Region).

Results:

LGX

Log

Please read:

Vadim

P.S. If you really want to get help - please do what is recommended!

[former_member200327]

Hi Silu,

Is name of your RATE Model RATE-BUD?

Regards,

Gersh

[former_member186338]

Hi Gersh,

Looks like - in the post with UJKT log I don't see errors with the line:

*LOOKUP RATE-BUD

B.R. Vadim

[former_member200327]

Agree, but I never saw a dash in Model name and just looking at the script hard to imagine what else can be wrong. If this is correct Model name then we definitely need latest UJKT log.

Gersh

[former_member186338]

Not an issue - just created:

Vadim

[former_member200327]

Can you try it in LOOKUP?

[former_member186338]

Ups, looks like you are right!

Test:

*LOOKUP RATE-BUD

*DIM R_ACCT="AVG"

*DIM R_ENTITY="GLOBAL"

*DIM C_RUB: INPUTCURRENCY="RUB"

*ENDLOOKUP

Result:

UJK_VALIDATION_EXCEPTION:Invalid lookup structure: Invalid appl name

With RATE - everything is fine!

It means that UJKT log provided was ...

Vadim

[former_member200327]

I'd like to hope that when UJKT ran there were other errors in the script so it didn't come to LOOKUP check.

[Former Member]

Hi

UJKT Result for testing taken two currencies CNY, EUR need to convert into SGD. No records generated

Code attached in screen shot

LOG: FILE:\ROOT\WEBFOLDERS\BIOSENSORS \ADMINAPP\SALESPLAN\TEST.LGF USER:V-SREEVANI APPSET:BIOSENSORS APPLICATION:SALESPLAN [INFO] GET_DIM_LIST(): I_APPL_ID="SALESPLAN", #dimensions=17 ACCOUNT,CATEGORY,COUNTRY,CURRENCY,DC,ENTITY,MATTYPE,MEASURES,PRODH1,PRODH2,PRODH3,PRODH4,REVENUE,SALESMATRIX,SALESREP,SEGMENT,TIME

#dim_memberset=0

REC :700*LOOKUP(VAR1)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 2.00 ms. 0  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: RATE-BUD

QUERY TIME : 1.00 ms. 24  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

NO RECORDS GENERATED.

CALCULATION END.

[INFO] GET_DIM_LIST(): I_APPL_ID="SALESPLAN", #dimensions=17 ACCOUNT,CATEGORY,COUNTRY,CURRENCY,DC,ENTITY,MATTYPE,MEASURES,PRODH1,PRODH2,PRODH3,PRODH4,REVENUE,SALESMATRIX,SALESREP,SEGMENT,TIME

#dim_memberset=0

REC :800*LOOKUP(VAR2)

CALCULATION BEGIN: QUERY PROCESSING DATA

QUERY TIME : 1.00 ms. 0  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: RATE-BUD

QUERY TIME : 0.00 ms. 24  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

NO RECORDS GENERATED. CALCULATION END.

Regards

Silu

[former_member200327]

Hi Silu,

I don't know why you keep changing what you have in that LOOKUP, but this log shows that you don't have data in your Model for  CATEGORY = BUDGET, REVENUE = ASP and CURRENCY = SNY or EUR.

Please check your data and scoping.

Regards,

Gersh

[Former Member]

Hi

Earlier script logic getting error in *REC, so i changed it. I attached my Exchange rate values as JPG

this is minor problem to fix, but taking long time to resolve it, I'm not getting any help in forums to fix this issue, rotating round and round,

can any one help, this is urgent and need to fix as soon as possible

concept is User enter data in any currency (for ex: user can select currency CNY or EUR or JPY any currency) in Input Schedule, after saving data this currency value need to convert into Local currency (for ex: SGD) of corresponding Company code (E2100.

Ex: Exchange Rate Table data

INPUTCURRENCY      LOCALCURRCY     EXCHANGERATEVAlue

     CNY                                 SGD                    0.2084

     EUR                                 SGD                    1.6115

     JPY                                  SGD                     0.0117

If user enters 500 in CNY currency then this should convert into SGD as 500/0.2084 = 2399.444 (WHEN CATEGORY IS BUDGET                                                                                                                                                                              WHEN REVENUE IS ASP)

I'm trying lot to fix it, is there any one to give complete script logic code to fix this issue.

Regards

Silu

[Former Member]

Hi

Finally I got Solution, I'm able to convert currencies from user entered currency value to Local currency of corresponding Company code

Ex: user entered in CNY currency for Comapny code E2200 and this value converted to SGD which is local currency of company code E2200

similarly for EUR

find attachment of my SALES PLANNING MODEL

but only thing it is overwriting with SGD so we need to add one more dimension to differentiate the records.

But Script logic i'm adding multiple variables in LOOKUP, instead of IT if there is a feasible solution that will be helpful for me

Regards

Silu

[Former Member]

Hi

I wrote Script logic as I gave below and finally got solved, but it is all hard coded, can any one help me to make it as dynamic, i mean used multiple times WHEN for each currency

*SELECT(%LCUR%,"[CURRENCY]",ENTITY, ID=%ENTITY_SET%)

*LOOKUP RATE-BUD

*DIM CATEGORY=BUDGET

*DIM RATEID=BUD

*DIM SOURCECURR1:RATEENTITY=%LCUR%

*DIM SOURCECURR1:INPUTCURRENCY=CNY

*DIM SOURCECURR2:RATEENTITY=%LCUR%

*DIM SOURCECURR2:INPUTCURRENCY=EUR

*DIM SOURCECURR3:RATEENTITY=%LCUR%

*DIM SOURCECURR3:INPUTCURRENCY=HKD

*DIM SOURCECURR4:RATEENTITY=%LCUR%

*DIM SOURCECURR4:INPUTCURRENCY=JPY

*DIM SOURCECURR5:RATEENTITY=%LCUR%

*DIM SOURCECURR5:INPUTCURRENCY=SGD

*DIM SOURCECURR6:RATEENTITY=%LCUR%

*DIM SOURCECURR6:INPUTCURRENCY=USD

*DIM SOURCECURR7:RATEENTITY=GLOBAL

*DIM SOURCECURR7:INPUTCURRENCY=%LCUR%

*ENDLOOKUP

*WHEN CURRENCY

*IS CNY

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR1),CURRENCY=%LCUR%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*WHEN CURRENCY

*IS EUR

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR2),CURRENCY=%LCUR%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*WHEN CURRENCY

*IS HKD

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR3),CURRENCY=%LCUR%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*WHEN CURRENCY

*IS JPY

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR4),CURRENCY=%LCUR%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*WHEN CURRENCY

*IS SGD

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR5),CURRENCY=%LCUR%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*WHEN CURRENCY

*IS USD

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR6),CURRENCY=%LCUR%)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*WHEN CURRENCY

*IS LC

*WHEN CATEGORY

*IS BUDGET

*WHEN REVENUE

*IS ASP

*REC(EXPRESSION=%VALUE%*LOOKUP(SOURCECURR7),CURRENCY=GC)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

Regards

Silu