Need help with a Formula please

[Karl_Spratt]

Hi Community, 

We brand products into frames using a material group, but some of the data in the Item master has been setup incorrect, and they won't amend at source, so we have to create a formula in the workflow to correctly segment the frames (Sound familiar?? 😀)

Ok my problem is we need to use the Material number, but these again have been created with no correct sequencing.

So, I have items with the same characters but if an Item "begins with the Letter R or doesn't not end with a B or a C" these are items we need to reclassify, but we also have Fix Speed Models & Variable Speed Models to recode.  

I've tried to show the end game I'd like to see in the attached, can anyone please advise how to write the formula?

TIA,

Karl. 

[binuacs]

@Karl_Spratt Can you also provide the expected result based on your input file?

[Karl_Spratt]

Hi Binuacs,

Like to see this, please if possible.

Cheers,
Karl.

[Christina_H]

How do you know the first row needs to change?  It doesn't match any of the conditions you've included in the formula so far.  The material code doesn't contain R, B or C.

This is the formula I've used so far, but it doesn't correct that first row.

if [Frame]='F1 Premium_Fix Spd' then
(if Contains([Material],'R') then 'FML Fix and FML PKG fix'
elseif Right([Material],1) IN('B','C') then 'F1 Premium_Fix Spd'
else [Frame]
endif)
else
(if Contains([Material],'R') then 'FML RS  and FML RS PKG'
elseif Right([Material],1) IN('B','C') then 'F1 Premium_Var Spd'
else [Frame]
endif)
endif

[Karl_Spratt]

Hi Christina H

That why I was trying to write it as

if [Frame]='F1 Premium_Fix Spd' and "If it doesn't end in a B or C then 'FML Fix and FML PKG fix'

Because they setup with the Material Nomenclature all over the place to be honest. 

Is it possible to write If it doesn't end in in the formula?

Cheers,

Karl. 

[binuacs]

@Karl_Spratt your first condition is 

IF [Frame] Contains("F1 Premium_Fix Spd") and Material contains R  THEN "FML Fix and FML PKG fix" that is not satisfying the material field value : A34905674 

[binuacs]

@Karl_Spratt Are you expecting the formula to write based on your expected output values?

[binuacs]

@Karl_Spratt Can you check the below formula works for you

IF Contains([Frame], 'F1 Premium_Fix Spd')  AND (EndsWith([Material], 'B') OR EndsWith([Material], 'C')) THEN  "F1 Premium_Fix Spd" 
ELSEIF contains([Frame],"F1 Premium_Fix Spd") AND  !Contains([Material], 'R') THEN "FML Fix and FML PKG fix"
ELSEIF Contains([Frame], "F1 Premium_Var Spd") and Contains([Material], 'R') THEN "FML RS  and FML RS PKG" 
ELSEIF Contains([Frame],'F1 Premium_Var Spd') AND (EndsWith([Material], 'B') OR EndsWith([Material], 'C'))  THEN  "F1 Premium_Var Spd" 
ELSEIF Contains([Frame], 'F1 Premium_Fix Spd') AND !EndsWith([Material], 'B')  AND !EndsWith([Material], 'C')  THEN 'FML Fix and FML PKG fix'
ELSE Null()
ENDIF

[Karl_Spratt]

Hi Binuacs,

I used most of you formula, I had to amend the NUll at the end to Frame as I have to also pull in the non F1 Frames into the report, but the issue then I have is where its a F1 Frame coded but the Material item doesn't end in a B or a C, is it possible to write in alteryx the formula logic doesn't EndsWith([Material], 'B orC'??? So I would code a Material item like A34905120 as FML RS and FML RS PKG, currently in my query its coming back as F1 Premium_Var Spd

IF Contains([Frame], 'F1 Premium_Fix Spd') AND (EndsWith([Material], 'B') OR EndsWith([Material], 'C')) THEN "F1 Premium_Fix Spd" ELSEIF contains([Frame],"F1 Premium_Fix Spd") AND !Contains([Material], 'R') THEN "FML Fix and FML PKG fix" ELSEIF Contains([Frame], "F1 Premium_Var Spd") and Contains([Material], 'R') THEN "FML RS and FML RS PKG" ELSEIF Contains([Frame],'F1 Premium_Var Spd') AND (EndsWith([Material], 'B') OR EndsWith([Material], 'C')) THEN "F1 Premium_Var Spd" ELSEIF Contains([Frame], 'F1 Premium_Fix Spd') AND StartsWith([Material], 'R') THEN 'FML Fix and FML PKG fix' ELSE [Frame]
ENDIF

Thanks

Karl 

[binuacs · Accepted solution]

@Karl_Spratt you can  the formula logic doesn't EndsWith([Material], 'B orC' like below. It is included in the above formula, which i provided

!EndsWith([Material], 'B')  AND !EndsWith([Material], 'C')