Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have several different materials to I need to remove the last 2 characters to convert from Loaner Material to Material. I was able to use the Left and Index functions in an IF statement which worked for the first 4 loops, but I am not able to get it to work on the last loop.
Is there a limit to how many IF loops I can create?
Or does anyone know of a better way to set up this function?
IF(isnull(Left(Material, Index(Material,'=A',-1)-1)),
if(isnull(Left(Material, Index(Material,'-A',-1)-1)),
if(ISNULL(Left(Material, Index(Material,'-LNR',-1)-1)),
if(isnull(Left(Material, Index(Material,'-S',-1)-1)),
if(isNull(Left(Material, Index(Material,'=S',-1)-1)),
Left(Material, Index(Material,'=S',-1)-1),1),
Left(Material, Index(Material,'-S',-1)-1)),
Left(Material, Index(Material,'-LNR',-1)-1)),
Left(Material, Index(Material,'-A',-1)-1)),
Left(Material, Index(Material,'=A',-1)-1)) as Material,
@LehiVitor you can try with Pick match approach
Pick(WildMatch(LoanerMaterial,'*-A*','*=A*','*-S*','*=S*','*-LNR*')+1,
LoanerMaterial,
Left(LoanerMaterial,Index(LoanerMaterial,'-A')-1),
Left(LoanerMaterial,Index(LoanerMaterial,'=A')-1),
Left(LoanerMaterial,Index(LoanerMaterial,'-S')-1),
Left(LoanerMaterial,Index(LoanerMaterial,'=S')-1),
Left(LoanerMaterial,Index(LoanerMaterial,'-LNR')-1)) as Material
@LehiVitor you can try with Pick match approach
Pick(WildMatch(LoanerMaterial,'*-A*','*=A*','*-S*','*=S*','*-LNR*')+1,
LoanerMaterial,
Left(LoanerMaterial,Index(LoanerMaterial,'-A')-1),
Left(LoanerMaterial,Index(LoanerMaterial,'=A')-1),
Left(LoanerMaterial,Index(LoanerMaterial,'-S')-1),
Left(LoanerMaterial,Index(LoanerMaterial,'=S')-1),
Left(LoanerMaterial,Index(LoanerMaterial,'-LNR')-1)) as Material