LOOKUP (Standard only)

LOOKUP (Standard only)

BPC

Script Logic

LOOKUP

LOOKUP (Standard only)

LOOKUP

• 可在相同或不同模型下进行数据读取，比如在translate一些汇率转换中会需要用此逻辑脚本。WHEN/ENDWHEN语法紧跟在LOOKUP/ENDLOOKUP之后，用来计算一个新的值或者定义要被计算的条件。
• LOOKUP机制原理是对应了待被处理记录与预设值相对应数值之间的关系。

Syntax

1. *LOOKUP （app name——区分大小写） 

   ---

2. *DIM [{LookupID}:{Diememtion}] (读取范围） 

   ---

3. *DIM MEASURES = YTD/PERIODIC （当两个不同时） 

   ---

4. *ENDLOOKUP 

   ---

5. ---- 

   ---

6. *WHEN ACCOUNT 

   ---

7. *IS * 

   ---

8. *REC {factor = LOOKUP{VARIABLE},INPUTCURRENCY=USD} 

   ---

9. *ENDWHEN 

   ---

10. 单个变量 

    ---

---

例子：

Rate模型中待取的数如下

Category	InputCurrency	R_ACCT	R_ENTITY	TIME	SignData
ACTUAL	EUR	AVG	GLOBAL	2017.01	0.78
ACTUAL	USD	AVG	GLOBAL	2017.02	1.0000

我们需要在ZRB模型中取出汇率用于计算以LC的换算价格。

TIME	P_ACCT	ENTITY	P_ACTIVITY	CATEGORY	Currency	P_DATASRC	SignData
2017.01	410000	C101	NONE	ACTUAL	LC-RMB	INPUT	800

用户在对话框中选择相应的汇率，使LC转变为目标汇率

script

1. *SELECT(%CUR%, "[ID]", RPTCURRENCY, [REPORTING] = Y) 

   ---

2. *LOOKUP Rates 

   ---

3. *DIM CATEGORY = Actual 

   ---

4. *DIM R_ACCOUNT = AVG 

   ---

5. *DIM R_ENTITY = Global 

   ---

6. *DIM TIME = TIME 

   ---

7. // *DIM MEASURES = "PERIODIC" 

   ---

8. *FOR %LOOP_CUR% = %CUR% 

   ---

9. *DIM C_%LOOP_CUR%:INPUTCURRENCY = %LOOP_CUR% 

   ---

10. *NEXT 

    ---

11. *DIM RATESOURCE:CATEGORY = Actual 

    ---

12. *DIM RATESOURCE:R_ENTITY = Global 

    ---

13. *DIM RATESOURCE:INPUTCURRENCY = RB_ENTITY.CURRENCY 

    ---

14. *DIM R_ACCOUNT = RB_ACCOUNT.RATETYPE 

    ---

15. *DIM RATESOURCE:TIME = TIME 

    ---

16. *ENDLOOKUP 

    ---

17. *XDIM_MEMBERSET RBPRODUCT = CK2000 

    ---

18. *XDIM_MEMBERSET RPTCURRENCY = LC 

    ---

19.  

    ---

20. *WHEN RB_ACCOUNT 

    ---

21. *IS 410000 

    ---

22. *FOR %LOOP_CUR% = %CUR%  

    ---

23. *REC(EXPRESSION = %VALUE% * LOOKUP(C_%LOOP_CUR%)/LOOKUP(RATESOURCE), RPTCURRENCY = %LOOP_CUR%) 

    ---

24. *NEXT 

    ---

25. *ENDWHEN 

    ---

 

2017-11-13 11_13_32-translate - Excel

 

计算逻辑：

1）判断C100的本位币LC是什么
2）汇率表以何为本位币
3）目标模型LC汇率转换未汇率表本位币 （ACCOUNTLC/变量：entity.currency)

• USD=%VALUE%/(变量：entity.currency=LC)*USD_RATES ;
• RMB =%VALUE%/(变量：entity.currency=LC)*RMB_RATES

4. 以下为不使用变量与loop情况下的script

1. *LOOKUP Rates 

   ---

2.  

   ---

3. *DIM RATEEUR:CATEGORY=Actual 

   ---

4. *DIM RATEEUR:R_ENTITY=Global 

   ---

5. *DIM RATEEUR:INPUTCURRENCY=EUR 

   ---

6. *DIM RATEEUR:TIME=TIME 

   ---

7.  

   ---

8. *DIM RATEUSD:CATEGORY=Actual 

   ---

9. *DIM RATEUSD:R_ENTITY=Global 

   ---

10. *DIM RATEUSD:INPUTCURRENCY=USD 

    ---

11. *DIM RATEUSD:TIME=TIME 

    ---

12.  

    ---

13. *DIM RATERMB:CATEGORY=Actual 

    ---

14. *DIM RATERMB:R_ENTITY=Global 

    ---

15. *DIM RATERMB:INPUTCURRENCY=RMB 

    ---

16. *DIM RATERMB:TIME=TIME 

    ---

17.  

    ---

18. *DIM RATESOURCE:CATEGORY=Actual 

    ---

19. *DIM RATESOURCE:R_ENTITY=Global 

    ---

20. *DIM RATESOURCE:INPUTCURRENCY=RB_ENTITY.CURRENCY 

    ---

21. *DIM R_ACCOUNT=RB_ACCOUNT.RATETYPE 

    ---

22. *ENDLOOKUP 

    ---

23. *XDIM_MEMBERSET RBPRODUCT = CK2000 

    ---

24. *XDIM_MEMBERSET RPTCURRENCY =LC //在UJKT中多次执行也不会累积计算 

    ---

25. *WHEN RB_ACCOUNT 

    ---

26. *IS *  

    ---

27. *REC(EXPRESSION=%VALUE%*LOOKUP(RATEEUR)/LOOKUP(RATESOURCE),RPTCURRENCY=EUR) 

    ---

28. *REC(EXPRESSION=%VALUE%*LOOKUP(RATEUSD)/LOOKUP(RATESOURCE),RPTCURRENCY=USD) 

    ---

29. *REC(EXPRESSION=%VALUE%*LOOKUP(RATERMB)/LOOKUP(RATESOURCE),RPTCURRENCY=RMB)  

    ---

30. *ENDWHEN  

    ---

转载于:https://www.cnblogs.com/sxl6666/p/8134738.html