%TIME_SET%.YEAR

[Former Member]

Hi,

I have a doubt. I would to develop a dynamic scriptlogic but I have problems to recover time set value:

*SELECT(%MYVAR%,"[ID]",TIME,"[HLEVEL]='3' AND ='MONTH' AND ='%TIME_SET%.YEAR'")

*XDIM_MEMBERSET TIME = %MYVAR%

*WHEN TIME.PERIOD

*IS "INPUT"

*FOR %ThisMeses%=%MYVAR%

*REC(Time=%ThisMeses%,EXPRESSION=%VALUE%/12)

*NEXT

*ENDWHEN

*COMMIT

I believe that the problem is in this line:

*SELECT(%MYVAR%,"[ID]",TIME,"[HLEVEL]='3' AND ='MONTH' AND ='%TIME_SET%.YEAR'")

Because, when I execute loggic debugger, the system show me that:

No records returned by *SELECT(%MYVAR%,"[ID]",TIME,"[HLEVEL]='3' AND ='MONTH' AND ='2010.INPUT.YEAR'")

I wish that appear the value 2010 when I make the select. In Time dimensión, the value of the column Year of 2010.INPUT is 2010.

Any idea?

Thanks!

[Former Member · Accepted solution]

Hi,

Try using

*SELECT(%MYVAR%,"ID",TIME,"LEVEL='MONTH' AND YEAR='%TIME_SET%.YEAR'")

How does this go?

[Former Member · Accepted solution]

It show me the same error:

No records returned by *SELECT(%MYVAR%,"ID",TIME,"LEVEL='MONTH' AND YEAR='2010.INPUT.YEAR'")

Thanks

[Former Member · Accepted solution]

You might want to select a property from your TIME dimension that has the values you want.

*SELECT(%TCM%, "CMTH", TIME, "ID='%TIME_SET%'")

*SELECT(%TCY%, "CYEAR", TIME, "ID='%TIME_SET%'")

*XDIM_MEMBERSET TIME = %TCY%,%TCM%

Edited by: Carlos Sandoval on May 16, 2011 11:23 PM

[Former Member · Accepted solution]

Hi,

Please try

*SELECT(%MYVAR%,"ID",TIME,"LEVEL='MONTH' AND YEAR='2010'")

Does this work properly

[Former Member · Accepted solution]

Hi,

Please try

*SELECT(%MYVAR%,"ID",TIME,"LEVEL='MONTH' AND YEAR='2010'")

Does this work properly

-

---

Yes, this source is correctly but I want that my source would be dynamic. In this case, if I want to take in concern another ages, I should copy&paste the same source but changed the value of Year.

I would like to make one query that It works correctly for each year but I prefer not copy it for each year. I would like dynamic one.

Thanks

[Former Member · Accepted solution]

You might want to select a property from your TIME dimension that has the values you want.

*SELECT(%TCM%, "CMTH", TIME, "ID='%TIME_SET%'")

*SELECT(%TCY%, "CYEAR", TIME, "ID='%TIME_SET%'")

*XDIM_MEMBERSET TIME = %TCY%,%TCM%

-

-

---

It doesn´t work correctly because, in the table mbrTime of the database, It doesn`t exist this column " CYEAR ".

[Former Member · Accepted solution]

Hi,

What have you maintained in the YEAR property of the member in TIME_SET?

What happens if you use:

*SELECT(%MYYEAR%,"YEAR",TIME,"ID=%TIME_SET%")

*SELECT(%MYVAR%,"ID",TIME,"YEAR=%MYYEAR%")

[Former Member · Accepted solution]

Hi,

What have you maintained in the YEAR property of the member in TIME_SET?

What happens if you use:

*SELECT(%MYYEAR%,"YEAR",TIME,"ID=%TIME_SET%")

*SELECT(%MYVAR%,"ID",TIME,"YEAR=%MYYEAR%")

-

---

Whe I try to validate and save the logic, Sap error appears:

Incorrect syntax near 'TIME_SET' . in: select YEAR from mbrTime where ID=%TIME_SET%

I have another doubt. Does exist one function that I could split one data ?

For example:

Time_Set= 2010.JAN // Function SubString(0,4)=2010.

It could be posible ?

Thanks

[Former Member · Accepted solution]

No, that is not possible in script logic.

Please try this:

*SELECT(%MYYEAR%,"YEAR",TIME,"ID='%TIME_SET%'")

*SELECT(%MYVAR%,"ID",TIME,"YEAR='%MYYEAR%'")

[Former Member · Accepted solution]

No, that is not possible in script logic.

Please try this:

*SELECT(%MYYEAR%,"YEAR",TIME,"ID='%TIME_SET%'")

*SELECT(%MYVAR%,"ID",TIME,"YEAR='%MYYEAR%'")

-

-

---

This source doesn´t work when I execute it in the default logic. In the logicDebugger works correctly but, when I execute it in the default logic, trying to import a data, it doesn´t do anything.

[Former Member · Accepted solution]

Hi,

What do you see in the log?

[Former Member · Accepted solution]

I copy the log that the logic debugger show me :

Start time --->10:42:28 AM - Date:5/17/2011 (build code:254)

****************************************************************************************************

User:MADARRVMTSA08\SAPBPC

Appset:TORO

App:Capex

Logic mode:1

Logic by:

Scope by:

Data File:

Debug File:E:\BPC\DATA\WebFolders\TORO\Capex\PrivatePublications\SAPBPC\TempFiles\DebugLogic.Log

Logic File:PRUEBA_DIEGO.LGF

Selection:DIMENSION:Account|PRICE|DIMENSION:DataSrc|D_INPUT_FILE|DIMENSION:Invest_Type|CANEOTH|DIMENSION:Investment|FIACCACA|DIMENSION:LPU|L_1110|DIMENSION:Manufacturer|MAN_NOKIA|DIMENSION:Operator|970_CECO|DIMENSION:Project|PTAAM_MBB|DIMENSION:RptCurrency|LC|DIMENSION:Supplier|SUP_NOKIA|DIMENSION:Time|2010.INPUT|DIMENSION:Version|QFCIIIRED|

Run mode:1

Query size:2

Delim:,

Query type:0

Simulation:1

Calc diff.:1

Formula script:

Max Members:

Test mode:0

Is Modelling:1

Number of logic calls:1

-

---

Call no. 1, logic:E:\BPC\DATA\WebFolders\TORO\AdminApp\Capex\PRUEBA_DIEGO.LGF

-

---

-

---

Building sub-query 1

-

---

Query Type:0

Max members:

Region:

DIMENSION:ACCOUNT

PRICE

DIMENSION:DATASRC

D_INPUT_FILE

DIMENSION:INVEST_TYPE

CANEOTH

DIMENSION:INVESTMENT

FIACCACA

DIMENSION:LPU

L_1110

DIMENSION:MANUFACTURER

MAN_NOKIA

DIMENSION:OPERATOR

970_CECO

DIMENSION:PROJECT

PTAAM_MBB

DIMENSION:RPTCURRENCY

LC

DIMENSION:SUPPLIER

SUP_NOKIA

DIMENSION:TIME

2010.INPUT

DIMENSION:VERSION

QFCIIIRED

DIMENSION:TIME

2010.JAN,2010.FEB,2010.MAR,2010.APR,2010.MAY,2010.JUN,2010.JUL,2010.AUG,2010.SEP,2010.OCT,2010.NOV,2010.DEC

Loading TIME.PERIOD,TIMEID

Time to load properties:0.0 sec.

-

---

select TIMEID,SIGNEDDATA

into #tblTempLogic_990353

from tblFactCapex

WHERE ACCOUNT=N'PRICE' AND DATASRC=N'D_INPUT_FILE' AND INVEST_TYPE=N'CANEOTH' AND INVESTMENT=N'FIACCACA' AND LPU=N'L_1110' AND MANUFACTURER=N'MAN_NOKIA' AND OPERATOR=N'970_CECO' AND PROJECT=N'PTAAM_MBB' AND RPTCURRENCY=N'LC' AND SUPPLIER=N'SUP_NOKIA' AND TIMEID in (N'20100100',N'20100200',N'20100300',N'20100400',N'20100500',N'20100600',N'20100700',N'20100800',N'20100900',N'20101000',N'20101100',N'20101200') AND VERSION=N'QFCIIIRED'

insert into #tblTempLogic_990353 (TIMEID,SIGNEDDATA)

select TIMEID,SIGNEDDATA

from tblFactWBCapex

WHERE ACCOUNT=N'PRICE' AND DATASRC=N'D_INPUT_FILE' AND INVEST_TYPE=N'CANEOTH' AND INVESTMENT=N'FIACCACA' AND LPU=N'L_1110' AND MANUFACTURER=N'MAN_NOKIA' AND OPERATOR=N'970_CECO' AND PROJECT=N'PTAAM_MBB' AND RPTCURRENCY=N'LC' AND SUPPLIER=N'SUP_NOKIA' AND TIMEID in (N'20100100',N'20100200',N'20100300',N'20100400',N'20100500',N'20100600',N'20100700',N'20100800',N'20100900',N'20101000',N'20101100',N'20101200') AND VERSION=N'QFCIIIRED'

and SOURCE = 0

insert into #tblTempLogic_990353 (TIMEID,SIGNEDDATA)

select TIMEID,SIGNEDDATA

from tblFAC2Capex

WHERE ACCOUNT=N'PRICE' AND DATASRC=N'D_INPUT_FILE' AND INVEST_TYPE=N'CANEOTH' AND INVESTMENT=N'FIACCACA' AND LPU=N'L_1110' AND MANUFACTURER=N'MAN_NOKIA' AND OPERATOR=N'970_CECO' AND PROJECT=N'PTAAM_MBB' AND RPTCURRENCY=N'LC' AND SUPPLIER=N'SUP_NOKIA' AND TIMEID in (N'20100100',N'20100200',N'20100300',N'20100400',N'20100500',N'20100600',N'20100700',N'20100800',N'20100900',N'20101000',N'20101100',N'20101200') AND VERSION=N'QFCIIIRED'

select tmpTable.TIMEID,sum(SIGNEDDATA) as SIGNEDDATA

from #tblTempLogic_990353 as tmpTable

group by tmpTable.TIMEID

drop table #tblTempLogic_990353

-

---

Time to load Source data:0.1 sec.

No record to process

SIMULATED call 1 completed in 0.2 sec.

SIMULATED Run completed in 0.3 sec.

I believe that in the logic debugger works correctly but when I execute it in the default logic via excel, it doesnt works.

[Former Member · Accepted solution]

If you leave out the quotes, like this:

*SELECT(%MYYEAR%,"YEAR",TIME,ID=%TIME_SET%)

*SELECT(%MYTIME%,"ID",TIME,YEAR=%MYYEAR% AND CALC='N')

The code works perfect for me. (BPC10 NW)

Regards,

Stijn