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 |
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
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?
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 |
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.
Which Alteryx tool could I use to establish the new 'Match key' column?
The formula tool.
[Shipping methods] + [Gift wrapped] + [Free samples requested]
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.
Thank you! I've got the tryout result below with 'Match key' column, and I was also wondering...
(1) how could I do 'count of x' (numeric format) in the 'Match key' column, so it shows the summary (number of X)? Refer to image1
(2) if certain columns (eg: coupon) come with numeric coupon code 12345 rather than just an x, what data type should I specify for the 'Match key' column? For example, the result of Match key column would then show 'xxx12345x'? Refer to image 2 and 3