Newbie: I'd like to double check if each of the sales transactions are accurately priced

[tsao88]

dataset 1: a list of sales transactions with various parameters including shipping methods, gift wrapped, free samples taken, etc. (the conditions is usually marked with 'X'

Sales record ID	Shipping methods	Gift wrapped	Free samples requested
1	x
2	x	x
3	x	x	x

dataset 2: official price list :if three of the above conditions are met, it's $10 for the services;
if two of the above conditions are met, it's $8;
if one of the above condition is met, it's $5

Shipping methods	Gift wrapped	Free samples requested	Package	Fee
x	x	x	A	$10
x	x		B	$8
x			C	$5

Could you please advise on which tools I might use to have Dataset 1 to scan through Dataset 2, scanning from left to right and top to bottom? I would appreciate any guidance you can provide?

Desired result

Sales record ID	Package	Fee
1	C	$5
2	B	$8
3	A	$10

[Carolyn]

Hi! Give this sample workflow a try. 

What I did was:

1. Add a Formula Tool to give me a "1" if an X is present in each of the columns and then total the 1s 

2. Join with DataSet 2 on the "Count of Xs"

Since you said that you're looking to see how many X's you have, regardless of if it's Gift Wrapped, etc, I went with a simpler approach to just say "tell me how many there are". 

If there were more categories, I'd probably use a Transpose Tool instead of a Formula Tool, but since this is just 3 columns, I'm being a little lazy and using Formula :) 

Also, in general, Joins on #s aren't the best practice (e.g. I'm joining on the # of X's), but in this case, since I'm expecting an integer (e.g. you can't have 2.5 X's), I'm good with it

[Carolyn]

Alternate option:

1. Multi-Field Formula Tool to replace all "X"s with a 1 and then change the Data Type to an Integer (INT16)
2. Transpose to swap everything from going across to going down
3. Summarize Tool to Total the X's (which are now 1s), grouped by Sales Order ID
4. Same Join as earlier

[tsao88]

Thank you!

(1) What if there are no comment elements between dataset1 and dataset2, how could I join the two datasets?

(2) What if the columns in dataset1 and dataset2 are called differently, would I need to manually change the names in order to 'join them by specific fields'?

(3) Could I use three sequential 'find and append' tools to look up and scan prices?

[TUSHAR050392]

I think you should make a new column called match key by simply concatenating the strings from your three columns like below. Once that is done, just join both the tables using match key and you will get the desired output once you drop unrequired columns

Sales record ID	Shipping methods	Gift wrapped	Free samples requested	Match key
1	x			x
2	x	x		xx
3	x	x	x	xxx

Shipping methods	Gift wrapped	Free samples requested	Package	Fee	Match key
x	x	x	A	$10	xxx
x	x		B	$8	xx
x			C	$5	x

[TUSHAR050392]

Here are my responses to your questions -

1. If there are no common elements, then datasets cannot be joined. You can manipulate the data to make something common.

2. If the fields are name differently, you will have to manually do the mapping in join tool as auto mapping won't work in this case.

3. I would suggest to look at my solution of creating a match key using your 3 columns as it will be more efficient way of approaching the solution and it is important to keep the performance of your workflow in mind while building solutions.

Thank you.

[tsao88]

Which Alteryx tool could I use to establish the new 'Match key' column? 

[cjaneczko]

The formula tool. 

[Shipping methods] + [Gift wrapped] + [Free samples requested]

[TUSHAR050392]

You can use the formula tool. Name the column as match key and formula will be Column1 + Column 2 + Column 3. Let me know if you need any more clarification. Hope the solution is helpful.