Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Simple formula question

Miki2021
8 - Asteroid

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!
 

15 REPLIES 15
AngelosPachis
16 - Nebula

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

 

AngelosPachis_0-1609679342170.png

 

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.

 

AngelosPachis_1-1609679774309.png

 

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
8 - Asteroid

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
16 - Nebula

Dear @Miki2021 ,

 

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

 

AngelosPachis_0-1609683754404.png

 

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.

AngelosPachis_1-1609683872666.png

 

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.

 

Version Change.gif

 

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

 

Hope that helps.

 

Regards,

 

Angelos

Miki2021
8 - Asteroid

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
20 - Arcturus
20 - Arcturus

@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
8 - Asteroid

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

Labels