Hello Anjana, 

If you want to keep the original XXXNOTALLOC, you just need to replace the whole formula by this :

'Actuals P&L Data Load'.'Busline Code - Warehouse'

It should keep the original busline value.

To test your change, what I can recommend is to put the old formula into comments using /* old formula */.

With this, it will be easy to revert back.

I hope this helps,

Franck

Hi Anjana,

If you are in a transaction list, I would recommend you to create a new property to test your formula if you don’t wish to impact the current model. Then if your results are satisfying, you can duplicate the formula back on your original property.

To do it, you can right click on the current property, and “insert new Property right”, select the appropriate type and you can now play with the formula. This will have no impact on your model.

If I read this code “XXXNOTALOCNG”, I believe it’s linked to 'Busline Code - Warehouse'.'is not alloc'

So it’s the second part of your formula where you’re saying:
if Busline."LOCCENTRAL" or 'Busline Code - Warehouse'.'is not alloc'

then if you item a Busline and it’s blank, take the 'Busline Code - Warehouse'

or if false, take the 'Busline Code - Warehouse'

So in any case, this second part returns the 'Busline Code - Warehouse'  for all results that are not matching the very first condition.

So the problem seems to be in this IFBLANK(ITEM(… condition

You’re trying to item a code made of 2 properties:

Original - Busline'&'Company Country - Warehouse'.Code

Which should propably render a Busline for when Busline Code - Warehouse'.'is not alloc'?

But as it’s saying just ifblank, there is no results pushed for when the item function actually finds a code.

Which is, I believe, the problem.

So I understand there are 3 conditions:

1. when (Busline."LOCCENTRALTN" OR Busline."LOCCENTRAL") AND 'Legal Entity'."JT_TN" = Busline."LOCCENTRALTNSSC"
2. when Busline."LOCCENTRAL" AND Legal Entity is not “JT_TN" = 'Busline Code - Warehouse'
3. when 'Busline Code - Warehouse'.'is not alloc' = either try to compose a Busline using ITEM OR if that does not match return 'Busline Code - Warehouse'

Ultimately, my suggestion would be to try:

if( ('Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRALTN" or 'Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRAL") and 'Actuals P&L Data Load'.id_company='Legal Entity'."JT_TN",Busline."LOCCENTRALTNSSC", if('Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRAL" or 'Actuals P&L Data Load'.'Busline Code - Warehouse'.'is not alloc', if( isdefined(item('Actuals P&L Data Load'.'Busline Code - Warehouse'.'Original - Busline'& 'Actuals P&L Data Load'.'Company Country - Warehouse'.Code,Busline)), item('Actuals P&L Data Load'.'Busline Code - Warehouse'.'Original - Busline'& 'Actuals P&L Data Load'.'Company Country - Warehouse'.Code,Busline), 'Actuals P&L Data Load'.'Busline Code - Warehouse') ,'Actuals P&L Data Load'.'Busline Code - Warehouse') )

It is saying if the item returns a result, then do the item.
If not, take the warehouse code.

or another way would be:

if( /*condition1*/ ( 'Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRALTN" or 'Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRAL" ) and 'Actuals P&L Data Load'.id_company='Legal Entity'."JT_TN", Busline."LOCCENTRALTNSSC", if( /*condition2*/ 'Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRAL"

, 'Actuals P&L Data Load'.'Busline Code - Warehouse'), if( /*condition3*/ 'Actuals P&L Data Load'.'Busline Code - Warehouse'.'is not alloc', if( /*Item a code*/ isdefined(item('Actuals P&L Data Load'.'Busline Code - Warehouse'.'Original - Busline'& 'Actuals P&L Data Load'.'Company Country - Warehouse'.Code,Busline)), /*if a code is found, return it*/ item('Actuals P&L Data Load'.'Busline Code - Warehouse'.'Original - Busline'& 'Actuals P&L Data Load'.'Company Country - Warehouse'.Code,Busline), /*if it’s blank*/ , 'Actuals P&L Data Load'.'Busline Code - Warehouse') , 'Actuals P&L Data Load'.'Busline Code - Warehouse') )

(If this last formula is not valid, it means I got the parenthesis wrong )

Hope this helps.

Hello

Im afraid that both the options dont work for me 

@Franck C  , yours is too simplistic, I still want to keep the TN SSC condition and the LOCCENTRAL conditions 

@Benoit , the formula you posted didnt change anything

If I maybe more clear - 

1. I want to keep the TN SSC condition (the first one)
2. I want to say if it is loccentral please add the country postfix 
3. I want to say if its NOTALLOC then DO NOT add the country postfix ( so i believe I need to split the part where it says if LOCCENTRAL And NOTALLOC because i dont want both to have the same result)

In my existing formula, i dont understand this Item stuff mainly - 

Hi,

Sorry, since we’re not familiar with your model and properties, it’s difficult to understand your need.

Please validate what you’re trying to achieve:

1. IF (Busline."LOCCENTRALTN" OR Busline."LOCCENTRAL") AND 'Legal Entity'."JT_TN"
   True =  Busline."LOCCENTRALTNSSC"
   False = Second condition
2. IF Busline."LOCCENTRAL" AND Legal Entity is not “JT_TN"
   True = 'Busline Code - Warehouse'
   False = Third condition
3. IF 'Busline Code - Warehouse'.'is not alloc'
   True =  'Busline Code - Warehouse'
   False = 'Busline Code - Warehouse'

About the ITEM function, it transforms text into a dimension item using an exact match between the text and a dimension property.
For example ITEM(“FY 22”, Year) will render the FY 22 dimension item.

Thank you.

Hi Benoit ! Thanks , almost there :) 

Please validate what you’re trying to achieve:

1. IF (Busline."LOCCENTRALTN" OR Busline."LOCCENTRAL") AND 'Legal Entity'."JT_TN"
   True =  Busline."LOCCENTRALTNSSC"
   False = Second condition
2. IF Busline."LOCCENTRAL" AND Legal Entity is not “JT_TN"
   True = 'Busline Code - Warehouse' ====> This needs to change to if its LOCCENTRAL and not JT_TN, then please give me LOCCENTRALXX (XX being the countrycode of the company country loaded in the data load, so if it was LOCCENTRAL loaded from Nigeria, it would use LOCCENTRALNG/ or if it was from Dubai, it would use LOCCENTRALDB)
   False = Third condition
3. IF 'Busline Code - Warehouse'.'is not alloc'
   True =  'Busline Code - Warehouse'
   False = 'Busline Code - Warehouse'

Hi,

Here is a new suggestion:

if( /*condition1*/

( 'Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRALTN" or 'Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRAL" ) and 'Actuals P&L Data Load'.id_company='Legal Entity'."JT_TN",

Busline."LOCCENTRALTNSSC",

if( /*condition2*/

'Actuals P&L Data Load'.'Busline Code - Warehouse' = Busline."LOCCENTRAL"

and 'Actuals P&L Data Load'.id_company <> 'Legal Entity'."JT_TN",

item('Actuals P&L Data Load'.'Busline Code - Warehouse'.'Original - Busline'& 'Actuals P&L Data Load'.'Company Country - Warehouse'.Code),

 'Actuals P&L Data Load'.'Busline Code - Warehouse') )

I don’t think the third condition is needed then as in any other cases you want to have the 'Busline Code - Warehouse'.

Hope this helps,

Best,

Hey @anjana ,

Did you manage to get forward with this one? :)

Best,

Hello Benji

Sorry its been a busy few weeks 

I just did, and this is the error I got - 

Function error: wrong number of arguments to ITEM. Expected 2 argument(s), but got 1 argument(s)

Not sure if I missed a space somewhere ?