Question

UPDATE a formula

  • 24 November 2023
  • 8 replies
  • 171 views
A
Rank
  • anjana Newly Minted Pigmenteer
  • Newly Minted Pigmenteer
  • 4 replies

I have a formula on my data load table, which automatically attributes a ‘clean’ code to an uploaded busline code. 

For eg. If a country NG uses XXXNOTALLOC , the clean code attributed to it is XXXNOTALOCNG

The formula is the following : 

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'
,ifblank(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'))

I dont want this to happen, I want to code to stay at XXNOTALLOC. I am not sure if this will have repurcussions on my data output and on my reports, but I would like to test modifying the formula first just in 1 cell

Any ideas on which part of the formula I edit ? 

 

 

8 replies

Franck C
Rank
Userlevel 4
Badge +4

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

 

Benoit
Rank
Userlevel 6
Badge +12

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.

Benoit - Pigment wizard
A
Rank

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 - 

Benoit
Rank
Userlevel 6
Badge +12

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.

 

Benoit - Pigment wizard
A
Rank

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'

 

Benoit
Rank
Userlevel 6
Badge +12

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,

Benoit - Pigment wizard
Benji
Rank
Userlevel 5
Badge +8

Hey @anjana ,

 

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

 

Best,

Benji
A
Rank

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 ? 

 

Reply


Ask your question here!

Terms & ConditionsCookie settings