cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shilpikumari_11 Frequent Visitor
Frequent Visitor

Multiple If Nested with TRIM

Hello Team,

 

I have a below formula which is giving error as "Cannot convert the value of type text to true/false", not sure what is going wrong, below is my formula and attached is my working sheet, Location filter is my output and SQIM Location normalized is my input. there is no option for attaching file Smiley Sad

 

Location Filter = IF ([SQIM Location normalized] = "OFS-DSC Jebel Ali" , "Dubai",
if([SQIM Location normalized] = "OFS-DSC Singapore", "Sing",
IF( [SQIM Location normalized] = "SC-DSC Logistics Houston" , "Hous",

IF ([SQIM Location normalized] = "Rott", "Rott",

IF ([SQIM Location normalized] = "KL Financial Hub Treasury" , "KLFH Treasury",
IF ([SQIM Location normalized] = "KL Financial Hub Tax" , "KLFH Tax",
IF ([SQIM Location normalized] = "KL Financial Hub General Acct" , "KLFH GA",
IF ([SQIM Location normalized] = "KL Financial Hub Shared" , "KLFH Shared",
IF ([SQIM Location normalized] = "GA" && [SQIM_Source]="KLFH Quest" , "KLFH GA",
if ([SQIM Location normalized] = "Tax" && [SQIM_Source]= "KLFH Quest" , "KLFH Tax",
if ([SQIM Location normalized] = "Treasury" && [SQIM_Source]= "KLFH Quest" , "KLFH Treasury",
IF ([SQIM Location normalized]= "OFS MEA HQ (Shared Folder)" , "KLFH Shared",
if ([SQIM Location normalized] = "GA" && [SQIM_Source]= "HFH Quest" , "HF GA",
if ([SQIM Location normalized] = "Tax" && [SQIM_Source]= "HFH Quest" , "HF Tax",
if ([SQIM Location normalized] = "Treasury" && [SQIM_Source]= "HFH Quest" , "HF Treasury",
if ([SQIM Location normalized] = "TAX" && [SQIM_Source]= "KLFH Quest" , "KLFH TAX",
if ([SQIM Location normalized] = "TAX" && [SQIM_Source]= "HFH Quest" , "HF TAX",
if ([SQIM Location normalized] = "SDM" && [SQIM_Source]= "HFH Quest" , "HF SDM",
if ([SQIM Location normalized] = "General" && [SQIM_Source] = "KLFH Quest" , "KLFH General",
if ([SQIM Location normalized] = "HFH" && [SQIM_Source]= "HFH Quest" , "KLFH General",
if ([SQIM Location normalized] = "Hub" && [SQIM_Function]= "Procurement" , "PnS Hub",
if ([SQIM Location normalized] = "HFH" && [SQIM_Source]= "HFH Quest" , "HFH Hub",
if ([SQIM Location normalized] = "KLFH GA" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH SDM" , [SQIM Location normalized],
if ([SQIM Location normalized] = "KLFH TAX" , [SQIM Location normalized],
IF( [SQIM Location normalized] ="KLFH Treasury" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KLFH Hub" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH Treasury" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH Hub" , [SQIM Location normalized],
if ([SQIM Location normalized] ="HFH GA" , [SQIM Location normalized],
if ([SQIM Location normalized] ="HFH TAX" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KLFH Accenture" ,[SQIM Location normalized],
if ([SQIM Location normalized] ="HFH Accenture" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KL Finance Hub General Acct" , "KLFH GA",
if ([SQIM Location normalized] ="KL Finance Hub Treasury","KLFH Treasury",
if ([SQIM Location normalized] ="KL Financel Hub Tax" , "KLFH TAX",
if ([SQIM Location normalized] = "PnS Hub" , "PnS Hub",
if ([SQIM_Area]= "NAR" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
if ([SQIM Location normalized]= "KL Finance Hub Tax" , "KLFH Tax",
if ([SQIM Location normalized]= "KLFH Shared" , "KLFH Shared",
if ([Geomarket (Code)]= "NGM" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
if ([Geomarket (Code)]= "NGM" && [SQIM_Area]= "NAR" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
IF( TRIM([SQIM Location normalized]) = "Houston Financial Hub Accenture (Mode 3)" && TRIM([SQIM_Source]) = "HFH Accenture" , "HFH Accenture",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Accenture (Mode 3)"  && TRIM([SQIM_Source]) = "KLFH Accenture" , "KLFH Accenture",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub General Acct" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH GA",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Shared" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH Shared",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Tax" && TRIM([SQIM_Source]) = "KLFH Quest" && "KLFH Tax",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Treasury" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH Treasury",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub ERM" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH ERM",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub FA\Inventory" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH FA\Inventory",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub General Acct" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH GA",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub Tax"   && TRIM([SQIM_Source]) = "TFH Quest" , "TFH TAX",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub Treasury"  && TRIM([SQIM_Source]) = "TFH Quest" , "TFH Treasury",
IF (TRIM([SQIM Location normalized]) = "Houston Financial Hub" && TRIM([SQIM_Source]) = "HFH Quest" , "HFH Hub",
IF ([SQIM Location normalized] = "Houston Financial Hub","HFH Hub",
if ([SQIM Location normalized] = " Houston Financial Hub ", "HFH Hub", "Field")
)))))))))))))))))))))))))))))))))))))))))))))))))))))))
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Johanno Member
Member

Re: Multiple If Nested with TRIM

Hi,

 

I really think you should separate the formula using VAR and instead of IF using SWITCH. You would get a much cleaner formula.

 

Is the data type "True/False" instead of text?

 

/Johan

Community Support Team
Community Support Team

Re: Multiple If Nested with TRIM

@shilpikumari_11,

 

As the error message mentioned, you should check if there're column value type true/false. In addtion, to make your code more clear and readable, you can use switch instead of if function.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Highlighted
Johanno Member
Member

Re: Multiple If Nested with TRIM

Hi,

 

I really think you should separate the formula using VAR and instead of IF using SWITCH. You would get a much cleaner formula.

 

Is the data type "True/False" instead of text?

 

/Johan

Community Support Team
Community Support Team

Re: Multiple If Nested with TRIM

@shilpikumari_11,

 

As the error message mentioned, you should check if there're column value type true/false. In addtion, to make your code more clear and readable, you can use switch instead of if function.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

shilpikumari_11 Frequent Visitor
Frequent Visitor

Re: Multiple If Nested with TRIM

All the column used are in Text data type only, I have changed to switch function still showing the same error. Smiley Sad