Process Variable Formula Code not working

[wigglyrat]

Hi: I am trying to put together a piece of code in the Process Variable Formula box that evaluates a variable. There are a set of variables that are being pulled in from a csv file 'B1' to 'B73' that I want to be added just as they are. The other variables, if one was for example 'OtherVariable' I want added as 'OtherVariable - (1234H) - h' - (with '1234H' being a different variable in the file - so for these it's just appending the second variable to it). All of the 'OtherVariable' variables are greater than three characters so the test I've made is to check if they begin with 'B' and are either 2 or 3 characters.

With this code below, I still get 'B1 (B1) -h' - i.e. the second variable appended when I only want 'B1'

Can someone please tell me where I am going wrong?

Thank you in advance.

Code: Select all

vHO = SUBST( vHeadOffice, 1, 1 );
If ( vHO @= 'B' & (LONG(vHO) = 3 % LONG(vHO) = 2 ));
vHeadOffice_ICEref= vHeadOffice;
else;
vHeadOffice_ICEref= vHeadOffice | ' (' | vHeadOfficeICERef | ') -h';
endif;

[wigglyrat]

oops I think I actually need vHeadOffice as the variable where the length is checked. Correction below, but this still does not work.

Code: Select all

vHO = SUBST( vHeadOffice, 1, 1 );
If ( vHO @= 'B' & (LONG(vHeadOffice) = 3 % LONG(vHeadOffice) = 2 ));
vHeadOffice_ICEref= vHeadOffice;
else;
vHeadOffice_ICEref= vHeadOffice | ' (' | vHeadOfficeICERef | ') -h';
endif;

[Steve Rowe]

Hi, Can you explain in what way it is not working and show the data it is not working with?

One thing it may be is that there are spaces at the start of your text which is breaking the logic, so suggest that you use Trim on variable values to ensure that any spaces are not an issue.

[wigglyrat]

Hi -

Thank you.

I have attached a sample of the data file.

Essentially I want head office for B1 - B73 to read as they are.

When I initially forgot to use 'vHeadOffice' in the if statement and was instead using 'vHO' (in the first code snipped I pasted) it skipped the test as you would expect and appended the vHeadOfficeICERef -h

Now using vHeadOffice in the corrected code, it is not adding them at all.

There don't seem to be any spaces so don't think that is the issue.

Many thanks in advance

SAMPLE.xlsx

(9.34 KiB) Downloaded 308 times

[Steve Rowe]

Hi,
I'm assuming that the column HeadOffice maps to vHeadOffice and HeadOfficeICERef maps to vHeadOfficeICERef

Reading through your code I can't see anything obvious wrong. I'd suggest putting some asciioutputs into your code so that you can use this to debug the code. The file will appear in the data directory of the TM1 instance.

Code: Select all

vHO = SUBST( vHeadOffice, 1, 1 );
	ASciioutput('test.cma' ,'Before_IF' , vHeadOffice, vHo,vHeadOfficeICERef );
If ( vHO @= 'B' & (LONG(vHeadOffice) = 3 % LONG(vHeadOffice) = 2 ));
	ASciioutput('test.cma' ,'IF_Pass' , vHeadOffice, vHo,vHeadOfficeICERef );
       vHeadOffice_ICEref= vHeadOffice;
else;
	ASciioutput('test.cma' ,'IF_Fail' , vHeadOffice, vHo,vHeadOfficeICERef );           
	 vHeadOffice_ICEref= vHeadOffice | ' (' | vHeadOfficeICERef | ') -h';
  
endif;

[wigglyrat]

Thank you for this suggestion. The output file looks as I'd expect B1...B73 pass, the others fail.

It is being added to Metadata which is where the code that builds the hierarchy is so this seems fine. Can't seem to work out why it's not picking up these numbers.

Thank you again.

[Steve Rowe]

Hi,
It's still not clear what is failing?
Do you mean that that it is going down the false channel of the If statement, as expected, but you are not getting the change to the variable you would expect?

What precisely do you mean by this statement?

Can't seem to work out why it's not picking up these numbers.

You've not mentioned anything to do with numbers up to now.

If you are doing a data load after using the code you have listed to update the dimension then double check that the manipulation you are doing on the data tab matches that on the metadata.

Cheers,