Script Logic - How to give condition based on value

[Former Member]

Hi Experts,

Here is my typical requirement.... During TD upload we are populating PC WITH DUMMY_PC AND FA WITH DUMMY_FA... At later point of time, we are populating these values with Correct PC and CC using below script where PC and CC are maintained as attributes of CC.

*XDIM_MEMBERSET PC = DUMMY_PC

*XDIM_MEMBERSET FA = DUMMY_FA

*XDIM_MEMBERSET TIME = %TIME_SET%

*WHEN CC.FA

*IS <> ""

*REC(EXPRESSION = %VALUE%, PC=CC.PC, FA=CC.FA)

*REC(EXPRESSION = 0)

*END WHEN

When I execute the above script for the first time... it worked as expected by making record with DUMMY_PC and DUMMY_FA as 0.

But when I try to run the same package for the 2nd time, I am facing with the below issue i.e. as the DUMMY_PC and DUMMY_FA values are 0, now it is updating the correct record (which is the end result of step 1) with 0.

To overcome this problem.. I changed the code as follows -

*WHEN CC.FA

*IS <>""

*REC(EXPRESSION=((%VALUE%<>0)*1),PC=CC.PC, FA=CC.FA)

*END WHEN.

Here I am facing the difficulty with syntax... Actually I want to write in such a way that if value <>0 then consider all such records and update with PC - CC.PC and FA = CC.FA and all other records i.e. record with value = 0.. I want to skip those records.

Could you help me out with correct syntax... Thanks in advance.

[former_member186338 · Accepted solution]

First of all it's a question of logic you want to implement...

What is the reason to run this script second time??? You copied the value and then cleared it in source...

If you want to copy only nonzero values then the script will be:

*LOOKUP SameCube //put the correct cube name!

*DIM PCFA:PC=CC.PC

*DIM FA=CC.FA

*ENDLOOKUP

*XDIM_MEMBERSET PC = DUMMY_PC

*XDIM_MEMBERSET FA = DUMMY_FA

*XDIM_MEMBERSET TIME = %TIME_SET%

*SELECT(%CCPCFA%,[ID],CC,[PC]<>"" AND [FA]<>"")

*XDIM_MEMBERSET CC = %CCPCFA% // Only CC with nonempty properties PC and FA

*WHEN CC

*IS *

*REC(EXPRESSION = (%VALUE%==0) ? LOOKUP(PCFA) : %VALUE%, PC=CC.PC, FA=CC.FA)

*REC(EXPRESSION = 0)

*END WHEN

LOOKUP will get a target value and in case of zero will write target over target - no change!

Vadim

[former_member186338 · Accepted solution]

And I agree with Gersh proposal - don't clear the original value. Use offset in Audit member or simply load to special CATEGORY...

[Former Member · Accepted solution]

Hi Kalinin,

Thank you for the response and providing the logic...

As an alternative... by running LIGHT Optimization after execution of the package also helpful.

[former_member186338 · Accepted solution]

Light optimization has to be configured to eliminate zero's... but I don't think it's a right way!

[Former Member · Accepted solution]

You can't base real life design on the fact that LO ran or not. You have to lock out all users from the system to run it hence you can't expect it to be ran after every script.

[Former Member]

This is not a problem of a wrong syntax, but a problem of a wrong approach.

If you load data to dummy members you shouldn't erase them - you have to offset them. This way no matter how many times you run that script you get same result.

To implement this you have to utilize one more Dimension, usually DataSrc or Audit, and populate it with special value, say LOAD. Then script takes values from LOAD and posts opposite values to member MOVE with all other Dimensions having same members. In reports you use total of LOAD + MOVE.

This way you don't have to check amounts and keep track of what was loaded and what moved.