How to read Dimesnios ID based on property of another dimension

[Former Member]

Hi Gurus,

I have   Dimension1 : EMPLOYEE   Job is property of Dimension: EMPLOYEE

             DImension2: JOB

My requirement is to read the Employees from EMPLOYEE dimension based on the property of JOB .

My Scoping  defined is like this . The code is working fine. But here I hardcoded JOB. I want to process the all the JOBs and read the EMPLOYEE of all the JOBs. Can anyone help me to the syntax of SELECT statement to read the Employees of for each JOB.

*SELECT (%EMP%,[ID],EMPLOYEE,[JOB]= 3001149)

*XDIM_MEMBERSET DATATAG= DT_PEXP_TMAL
*XDIM_MEMBERSET VER = WV1
*XDIM_MEMBERSET CUR= USD
*XDIM_MEMBERSET HRACCT = ALLOC_PCT
*XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE
*XDIM_MEMBERSET RECEIVER_CC = NO_RECEIVER_CC
*XDIM_MEMBERSET COSTCENTER = NO_COSTCENTER
*XDIM_MEMBERSET JOB = 3001149
*XDIM_MEMBERSET WBS = NO_WBS
*XDIM_MEMBERSET SKF = NO_SKF

*SELECT(%TM1%,ID,TIME,"[CALC] = N" AND [YEAR]=2015)
*SELECT (%EMP%,[ID],EMPLOYEE,[JOB]= 3001149)

*WHEN HRACCT
*IS ALLOC_PCT
*FOR %TIME% = %TM1%
*FOR %EMPLOYEE% = %EMP%

*REC(FACTOR=1, TIME=%TIME%, EMPLOYEE = %EMPLOYEE%)

*NEXT
*NEXT

*ENDWHEN
*COMMIT

[former_member186338 · Accepted solution]

I do not understand what do you want to achieve with this script!

Can you provide full info in line with

If you want to copy value from:

EMPLOYEE = NO_EMPLOYEE

JOB - list of JOB members

To

EMPLOYEE=... list of EMPLOYEE members having JOB from source

Then use FOR NEXT with RUNALLOCATION

*SELECT(%JJ%,[ID],JOB,[CALC]=N)

*FOR %J%=%JJ%

*XDIM_MEMBERSET JOB=%J%

*XDIM_MEMBERSET ...

...

*RUNALLOCATION

*FACTOR=1

*DIM EMPLOYEE WHAT=NO_EMPLOYEE; WHERE=[JOB]=%J%

*ENDALLOCATION

*NEXT

Logic with time is not clear...

Vadim

P.S. And never use useless COMMIT!

[Former Member · Accepted solution]

Hi Vadim,

Thanks for your reply.

My requirement is like:

Source data:

Dimension   DImesnion         Dimension   Dimension

Job             EMPLOYEE        TIME           Account

JOb1          No_EMployee      2015.INP     100

I want to read all the employees of from JOb1 and save it to Employee diemnson. JOB is property of Employee Dimension, and also I want copy records for TIME from 2015. INP to  2015.01......2015.012.  I tried with Run allocation I am getting error message:   "Allocation Format error"

*XDIM_MEMBERSET DATATAG= DT_PEXP_TMAL
*XDIM_MEMBERSET VER = WV1
*XDIM_MEMBERSET CUR= USD
*XDIM_MEMBERSET HRACCT = ALLOC_PCT
*XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE
*XDIM_MEMBERSET RECEIVER_CC = NO_RECEIVER_CC
*XDIM_MEMBERSET COSTCENTER = NO_COSTCENTER
*XDIM_MEMBERSET WBS = NO_WBS
*XDIM_MEMBERSET SKF = NO_SKF

*SELECT(%JJ%,[ID],JOB,[CALC]=N)

*FOR %JOB%=%JJ%
//*XDIM_MEMBERSET JOB=%J%
*RUNALLOCATION
*FACTOR = 1
*DIM EMPLOYEE WHAT=NO_EMPLOYEE; WHERE=[JOB]=%JOB%;
*ENDALLOCATION
*NEXT

can you please help me to fix this error.

[Former Member · Accepted solution]

My Target data something like this:

JOb    EMployee        Time                                         Account

JOb1  emp1             2015.001  to    2015.012                 100

JOb1  emp2             2015.001  to    2015.012                   100      

JOb1  emp3             2015.001   to   2015.012                  100
JOb1  emp4             2015.001   to   2015.012                  100

[former_member186338 · Accepted solution]

Use %J% variable with provided *XDIM_MEMBERSET!

Put all scoping statements inside for next loop!

for time use the same approach

*SELECT(%TM1%,ID,TIME,"[CALC] = N" AND [YEAR]=2015)

DIM TIME WHAT=2015.INP; WHERE=%TM1%

vadim

[former_member186338 · Accepted solution]

P.s. Provide test results in UJKT

[Former Member · Accepted solution]

Hi Vadim,

In UJKT: I validate the code I am getting error

UJK_VALIDATION_EXCEPTION:Line No:3; Allocation Format Error

*SELECT(%JJ%,[ID],JOB,"[CALC]=N")

*SELECT(%TM1%,ID,TIME,"[CALC] = N" AND [YEAR]=2015)

*FOR %J%=%JJ%

*XDIM_MEMBERSET DATATAG= DT_PEXP_TMAL

*XDIM_MEMBERSET VER = WV1

*XDIM_MEMBERSET CUR= USD

*XDIM_MEMBERSET HRACCT = ALLOC_PCT

*XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE

*XDIM_MEMBERSET RECEIVER_CC = NO_RECEIVER_CC

*XDIM_MEMBERSET COSTCENTER = NO_COSTCENTER

*XDIM_MEMBERSET WBS = NO_WBS

*XDIM_MEMBERSET SKF = NO_SKF

*XDIM_MEMBERSET JOB=%J%

*RUNALLOCATION

*FACTOR =1

*DIM EMPLOYEE WHAT=NO_EMPLOYEE; WHERE=[JOB]=%J%

*DIM TIME WHAT=2015.INP; WHERE=%TM1%

*ENDALLOCATION

*NEXT

[former_member186338 · Accepted solution]

Please test the script on the limited set of jjobs:

*FOR %J%=JOB1,JOB2 // use some real members

Provide UJKT screenshots and logs...

Vadim

[Former Member · Accepted solution]

I tried but still I am getting same error message I am attaching screenshot s from UJKT test results

Script error.xml

[former_member186338 · Accepted solution]

Please test the script for single job:

*SELECT(%TM1%,ID,TIME,"[CALC] = N" AND [YEAR]=2015)

*XDIM_MEMBERSET DATATAG= DT_PEXP_TMAL

*XDIM_MEMBERSET VER = WV1

*XDIM_MEMBERSET CUR= USD

*XDIM_MEMBERSET HRACCT = ALLOC_PCT

*XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE

*XDIM_MEMBERSET RECEIVER_CC = NO_RECEIVER_CC

*XDIM_MEMBERSET COSTCENTER = NO_COSTCENTER

*XDIM_MEMBERSET WBS = NO_WBS

*XDIM_MEMBERSET SKF = NO_SKF

*XDIM_MEMBERSET JOB=3000003

*RUNALLOCATION

*FACTOR =1

*DIM EMPLOYEE WHAT=NO_EMPLOYEE; WHERE=[JOB]="3000003"

*DIM TIME WHAT=2015.INP; WHERE=%TM1%

*ENDALLOCATION

And attach screenshots directly in the message, xml - terrible idea!

Vadim

P.S. I understand the reason of the error - quotes has to surround property "%J%"!

*SELECT(%JJ%,[ID],JOB,"[CALC]=N")

*SELECT(%TM1%,ID,TIME,"[CALC] = N" AND [YEAR]=2015)

*FOR %J%=%JJ%

*XDIM_MEMBERSET DATATAG= DT_PEXP_TMAL

*XDIM_MEMBERSET VER = WV1

*XDIM_MEMBERSET CUR= USD

*XDIM_MEMBERSET HRACCT = ALLOC_PCT

*XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE

*XDIM_MEMBERSET RECEIVER_CC = NO_RECEIVER_CC

*XDIM_MEMBERSET COSTCENTER = NO_COSTCENTER

*XDIM_MEMBERSET WBS = NO_WBS

*XDIM_MEMBERSET SKF = NO_SKF

*XDIM_MEMBERSET JOB=%J%

*RUNALLOCATION

*FACTOR =1

*DIM EMPLOYEE WHAT=NO_EMPLOYEE; WHERE=[JOB]="%J%"

*DIM TIME WHAT=2015.INP; WHERE=%TM1%

*ENDALLOCATION

*NEXT

[Former Member · Accepted solution]

Hi Vadim,

after i used the Quotes it is working but Employees are not reading. I am attaching log and test results and Model(Cube) data also.

Thanks for your help.

Script error.xml

[former_member186338 · Accepted solution]

Sorry, I am unable to read you xml attachments (already mentioned in my previous post)!

Use text for logs, and png for pictures. Attach pictures directly in the message using correct browser!

Vadim

[former_member186338 · Accepted solution]

Ups, I managed to open you xml - PLEASE NEVER USE IT! - in Word!

You have:

Your model is off line???

Please ensure that you have a working system!

And test first script without FOR/NEXT!

Vadim

[former_member186338 · Accepted solution]

And the script you are testing:

Is incorrect - compare it with my script! FOR is BEFORE XDIM_MEMBERSET!...

[Former Member · Accepted solution]

Hi Vadim,

Thanks for your help it is working now.  I want to populate Dimensions COSTCENER and SKF also for each Employee which are property's of EMPLOYEE Dimension. I have to write another script to populate these two properties or can we populate those two properties in the same logic. can you share your tought's on this.

Thanks again for your help.

Thanks

Best regards

SG

[former_member186338 · Accepted solution]

Multiple nested FOR/NEXT can be slow... Try a sequence!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I am trying to read the Property's of Dimension EMPLOYEE , COSTCENTER and SKF are property's of EMPLOYEE dimension. I want to populate those two property's for each employee

can you help me with correct syntax.

I have written the code like this"

*SELECT(%E%,ID,EMPLOYEE, [SKF]=SK205)

*FOR %EMP% = %E%

*XDIM_MEMBERSET DATATAG= DT_PEXP_TMAL

*XDIM_MEMBERSET VER = WV1

*XDIM_MEMBERSET CUR= USD

*XDIM_MEMBERSET HRACCT = ALLOC_PCT

*XDIM_MEMBERSET RECEIVER_CC = NO_RECEIVER_CC

*XDIM_MEMBERSET COSTCENTER = NO_COSTCENTER

*XDIM_MEMBERSET WBS = NO_WBS

*XDIM_MEMBERSET SKF = NO_SKF

*XDIM_MEMBERSET EMPLOYEE = %E%

*REC(FACTOR=1, COSTCENTER = %EMP%.COSTCENTER; SKF = %EMP%.SKF )

*NEXT

I am getting below error when I do the Execute(Simulate):

UJK_VALIDATION_EXCEPTION:Line 2: Unknown or unimplemented keyword "*REC(FACTOR=1, COSTCENTER = %E%.COSTCENTER; SKF ="

[former_member186338 · Accepted solution]

Sorry, but absolutely incorrect syntax! Please read manuals (at least help) and try again!

[gajendra_moond]

Hi S G

See if this helps.

Select the dimension whose property you wish to link with other dimension. This will link member of one dimension with the property of another.