Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

SOLVED

Formula question - IF contain or other solutions

Miki2021
7 - Meteor

Hi Alteryx community,

 

I tried to apply a formula to categorize many different invoice types. And I appreciate your input: 

 

Scenario:

In SAP raw data, we have different invoices with different invoice number. First two or three digits of the number depicts certain types of invoice. E.g.

 

invoice number of which first few digits start with: 

8xxxxxxxxx    invoice type A

44xxxxxxxx    invoice type B

49xxxxxxxx    also invoice type B

777xxxxxxx    invoice type C

001xxxxxxx    invoice type D 

else                Others

 

My formula:

IF Contains ([test], "8") THEN "invoice type A"
ELSEIF Contains([test], "44") THEN "invoice type B"
ELSEIF Contains([test], "49") THEN "invoice type B"
ELSEIF Contains([test], "777") THEN "invoice type C"
ELSEIF Contains([test], "0001") THEN "invoice type D"
ELSE "Others"
ENDIF

 

Issue:

The IF contain formula might also include scenarios where the specific numbers appear within the 10 digit invoice number, not limited to the first few digits. 

Maybe I should not use the IF contain formula.  Any bright idea here? 

 

Thanks!

AngelosPachis
14 - Magnetar

Hi @Miki2021 ,

 

You can try the startwith function instead of the contains, so your statement would be :

 

IF Startswith([test], "8") THEN "invoice type A"
ELSEIF Startswith([test], "44") THEN "invoice type B"
ELSEIF ...
ELSE "Others"
ENDIF

 

That will look for the numbers at the beginning of the invoice only.

 

Hope that helps, let me know if that worked for you.

 

Cheers,

 

Angelos 

Ben_H
10 - Fireball

Hi @Miki2021 

 

I think it would be easier to use the replace tool, something like this -

 

Ben_H_0-1613747351070.png

 

I created the rules as a seperate input like this -

 

Ben_H_1-1613747389562.png

 

I then feed the ones that must match the start of the ID into the first replace tool, any that don't match are then fed into the second replace to match the codes that can be anywhere in the ID.

 

This way you can easily manage your rules without having a massive nested IF statement.

 

Regards,

 

Ben

 

Miki2021
7 - Meteor

Hi @Ben_H 

Thanks for your input! Just found out a faster way to do the same! See above.

 

cheers,

Miki 

Labels