Need help with a Formula please

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.

Test Data.xlsx

Workflow

Accepted answers

binu_acs

@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')

All comments

binu_acs

@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.

Test Data.xlsx

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.

binu_acs

@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

binu_acs

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

binu_acs

@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

Formula_1007.yxmd

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

binu_acs

@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')

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703