cancel
Showing results for 
Search instead for 
Did you mean: 

Lookup Issue(updated)

Former Member
0 Kudos

Sorry, the last post was confusing so I am reposting.

Hello, could you please review the following script logic. the issue I am having is I need to multiply with the exchange rate of current month in the REC statement. but the way it is right now, it is multiplying with rate of previsous month. I tried the following(which WONT work)

*DIM TIME=%TIME_SET%

Also, please keep in mind that I cant change *when time is prior to %time_set%, as the actual script logic is lot bigger. thanks.

________________________________________

*LOOKUP RATE

*DIM RATESRC="RATEINPUT"

*DIM INPUTCURRENCY=COMPANY.CURRENCY

*DIM RATE="END"

*ENDLOOKUP

*SELECT(%AREGION%, "ID", "ACCOUNT","ASIAREGION='Y'")

*XDIM_MEMBERSET ACCOUNT=%AREGION%

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET DATASRC=ASREGION

*XDIM_ADDMEMBERSET DATASRC=Descendants(DATASRC.TOTAL_GAAP,99,LEAVES)

*XDIM_MEMBERSET RPTCURRENCY=USD,LC

*XDIM_MEMBERSET TIME=%TIME_SET%,PRIOR

*WHEN DATASRC

*IS ASREGION

*WHEN TIME

*IS PRIOR

*WHEN RPTCURRENCY

*IS LC

REC(FACTOR=1LOOKUP,DATASRC=ASREGION,RPTCURRENCY=USD,TIME=NEXT)

*ENDWHEN

*ENDWHEN

*ENDWHEN

Accepted Solutions (0)

Answers (1)

Answers (1)

krishna_priya1
Contributor
0 Kudos

Becaue %TIME_SET% is not working ,try to change lookup as below, which would retrieve current months rate.


*LOOKUP RATE
*DIM RATESRC="RATEINPUT"
*DIM INPUTCURRENCY=COMPANY.CURRENCY
*DIM CURRMONTH:TIME=TIME.ID
*DIM RATE="END"
*ENDLOOKUP 

or 

*LOOKUP RATE
*DIM RATESRC="RATEINPUT"
*DIM INPUTCURRENCY=COMPANY.CURRENCY
*DIM CURRMONTH:TIME=TIME
*DIM RATE="END"
*ENDLOOKUP

And in rec statement


*REC(FACTOR=1*LOOKUP(CURRMONTH),DATASRC=ASREGION,RPTCURRENCY=USD,TIME=NEXT)

Former Member
0 Kudos

I tried your approach, still the same result as before.

krishna_priya1
Contributor
0 Kudos

if 2011.FEB is selected by user , when statement in simple terms is

WHEN TIME

*IS 2011.JAN

REC(FACTOR=1LOOKUP(2011.FEB) ,........ ,TIME=2011.FEB) .

Is it that you are trying to do?

Former Member
0 Kudos

Yes, that is correct.

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Zack,

The current record you are looking at is the prior month (*WHEN TIME *IS PRIOR )

You need to add a GET into your FACTOR statement to look at the current period.

Try this:

REC(FACTOR=1LOOKUP*GET(TIME=NEXT),DATASRC=ASREGION,RPTCURRENCY=USD,TIME=NEXT)

Thanks,

John

Former Member
0 Kudos

John, that script was giving number in millions. and it was supposed to be $581. I tried with brackets around lookup and get but that didnt work either. The GOOD NEWS is I solved the problem. It feels like million bucks when I solve a tough problem, lol. Here is how:

I inserted this code between XDIM block and When block. This will take current month(or rather user entered month)'s rate and store it in iN memory variable #TMPRT. Then in the orignal when/endwhen statement, I used GET(CATEGORY=#TMPRT).

*WHEN DATASRC

*IS ASREGION

*WHEN TIME

*IS %TIME_SET%

*WHEN RPTCURRENCY

*IS LC

REC(FACTOR=1LOOKUP,CATEGORY=#TMPRT,TIME=PRIOR)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*GO