Simple formula question

[Miki2021]

Hi there,

I'm trying to segment the sales channel raw data by creating a very simple formula on Alteryx. 

The logic goes like this:

If column A contains text, the sales channel = "Website" 

If column B contains text, the sales channel = "Mobile App"

Else, the rest is "Shop"

Originally I tried to use a conditional formula (IF xx THEN xx ELSEIF xx THEN xx ELSE xx ENDIF), but learnt that I cannot set the condition with IsString formula. Appreciate any suggestions from the bright minds here. 

Cheers!
 

[AngelosPachis · Accepted solution]

@Miki2021 

By the looks of it, it seems that columns Item Name, Form Name and Supp Aux Part Number are not simultaneously populated for a single record (so if

Item Name contains a value, then the other two would be Null and so on)

Hence, in your formula tool, you can use a logic function that checks if a field is null, or even better if a field is not null. In Alteryx, Not is denoted with an exclamation mark so you can use the following expression

IF !IsNull([Item Name ]) then "Website"
ELSEIF !IsNull([Form Name]) THEN "Mobile App"
ELSEIF !IsNull([Supp Aux Part Num])  Then "3rd Option"
ELSE
//if all three are Null()
NULL()
ENDIF

which will check if the value in Item Name is not null (so there is something within that cell) and if that's the case will return the value "Website" else it will go on to the Form Name field and check if there is a value within there and so on.

I was not sure how fields A and B used previously are related to the current field names, but you can amend it as necessary.

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

Regards,

Angelos

[Miki2021]

Dear Angelos, 

@AngelosPachis 

Thanks indeed and that works! I used !IsEmpty finally though (coz some cells are labelled as null and some are simply empty cells in the original raw data).   

Yes, you are right that the fields are not simultaneously populated for a single record, so the sequence of the logic is key. 

Maybe a beginner question, how can I check the version of Alteryx (guess mine is Alteryx 2020.2)? When I opened your file, a message pops up saying that I am not using the latest version.  

cheers,

Miki 

[AngelosPachis]

Dear @Miki2021 ,

You can check the version you are on by clicking on Help > About

and you will have a window popping-up, giving you more details about your version of Designer. I'm currently on 2020.3 and occasionally work on 2020.4 which is the latest version.

That versioning issue you mentioned can really by frustrating at times, since if you are on older versions of Alteryx you may not always be allowed to open certain workflows. An easy fix to that, which will be quite handy to you in the future if you are on 2020.2, is that you can change the version of a workflow through a text editor (such as Notepad).

In the following Gif, I've used a text editor to change the version of my workflow from 2020.3 to 2020.2.

If you do that and then open your workflow, you shouldn't get any errors.

Hope that helps.

Regards,

Angelos

[Miki2021]

Hi @Qiu,

I also tried your solution but somehow the results are quite different (sometimes an order that is purchased through website was considered as shop). Thanks for your thoughtful answer!

Any chance to know what defines D+ in your formula (some of my raw data contains numeric value, so not all are text)? I am still a newbie so this formula is pretty new to me, but I'm keen to learn about it:)  

Cheers,

Miki

[Qiu]

@Miki2021 

\D Matches anything other than a decimal digit.

Here is a website I often use.

Like I said, if you could more sample data, we will be happy to take a look.

[Miki2021]

@Qiu 

I shared the sample data here:

Solved: Re: Simple formula question - Alteryx Community

Think I will try \W since the column contains both digits and text. Tks for sharing this and it helps me to learn about RegEx.