Assigning values based on multiple field condition

[l_blumberger]

Hi, I have been using the multi-field formula, summarize, and tile tool in various combinations to try to get the results I need, and I just can't seem to get there!

My dataset looks something like these first 3 fields and I would like to add the fourth field, where, if the customer has been to ONLY company X then assign a 0 to each instance of customer (at the different sites). If the customer has been to ONLY company Y then assign a 1 to each instance of customer and if the customer has been to BOTH company X and Y then assign a 2 to each instance of customer. 

Customer	Site	Company	New Field
A	1	X	0
A	2	X	0
A	3	X	0
A	4	X	0
B	4	Y	1
C	5	X	0
C	6	X	0
C	4	X	0
D	2	X	2
D	5	X	2
D	6	X	2
D	4	X	2
D	7	Y	2
D	8	Y	2

I used the tile tool (company as unique field and grouping by customer) to get a tile number. Then used the formula IF [Tile_Num] = 1 AND [Company] = "X" THEN 0 ELSEIF [Tile_Num] = 2 AND [Company] = "Y" THEN 1 ELSE 2 ENDIF

Unfortunately, this only applies a 2 to the instances of customer where Company is Y and not X.

I may be overcomplicating this, any suggestions?

Thanks in advance!

[RodL]

You will find there's always more than one way to approach the logic in Alteryx.

In your case I'm thinking you could get some extensive "elseif" additions to your logic, but when I'm dealing with something like you are wanting where you are really "aggregating" on the customer to identify your flag, I like to use the Summarize.

Attached is an example where you summarize and concatenate you companies and the test whether the combination exists of not. Admittedly this works for your simple data set and may be a bit more complex for your actual data, but maybe it will give you another idea on a direction to go.

[AndréB · Accepted solution]

Hi @l_blumberger,

Attached is one way to tackle it. Let me know if that helps. Essentially I took the approach of getting the # of times a different company appears per customer side by side with the original data. From there used an "if then else" statement to assign the appropriate value to each record.

-AndréB

[MarqueeCrew]

@l_blumberger,

I took an approach to this that uses an interim (boolean) step.  

Are you an X shopper for this transaction?  Y/N 

Are you a Y shopper for this transaction? Y/N

Next I found the Maximum result for any customer & setup a "RESULT" field that converts both to a 2, X only to 0 and Y only to 1.  I also default to Null if an error exists.

Try this and see how it works for you:

[RodL]

We all must have submitted at the same time.

To my point...there are always more than one way to approach a data problem in Alteryx!

[david_fetters]

Hey there,

I think the easiest way to do this is to ignore the excel row-by-agonizing-row mentality in favor of splitting the data as you need to use it.  To solve your problem, try thinking about the ability to split and merge streams to do what you're after. This data should probably be stored in more than 1 table (i.e. you should have a customers table that is uniqued on customers, a stores table uniqued on stores, and a visits table that is the junction of the two).

I've come up with a solution that does what you're after using a few more nodes.  You can restructure this in a couple of different ways depending on what you're after, but I feel like this best illustrates the sort of multi-dimensional thinking that works best with Alteryx, as opposed to the 2d train of thought common in excel formula development.  I'm sure there are more elegant ways of doing this (I came up with a few while writing this).

Here's a screenshot of the workflow (which i've attached so that you can inspect whats going on at each node):

So we start with your static text table.  We then filter that based on the company into the two paths, one for each company, before uniquing on customer.  The result is that we will have a list of unique visitors to CompanyX and unique visitors to companyY.  We then add a flag field to the row to indicate that the unique visitor has visited company X or Y.

Then we join the two summary data sets back to our original dataset on customer.  This means that any customer who visitied Company X will have a 1 for the [CompanyX] field on all of its rows (Because we join on customer) and any visitor to Company Y will have a 1 for [CompanyY].  We can then use a formula to generate a third field (called new field, like your sample data) and setup some quick logic there.  The formula node uses the following formula:

IF [CompanyX] = 1 && [CompanyY] != 1 THEN 0
ELSEIF [CompanyX] != 1 && [CompanyY] = 1 THEN 1
ELSEIF [CompanyX] = 1 && [CompanyY] = 1 THEN 2
ELSE Null() ENDIF

*Notice that we use = 1 and != 1 rather than = 1 and = 0.  This formula performs the logic you are looking for.  After that, deselect the extra helper fields you used to get here, and voila you've got your results in less than five minutes!  Workflow attached for reference.

[david_fetters]

Gents, we need to space out all this good information :)  I think we may have answered the question, and shown just how flexible and adaptive Alteryx is in the process.

[MarqueeCrew]

Many ways to skin a cat ()....

[l_blumberger]

Thank you for the repsonses! Yes, I ran into some issues using the summarize tool because my actual dataset incorporates other important fields. Great suggestions :)