Sales record & Product price list

[tsao88]

Hi -
I'd like to run datasetA (sales records with various parameters including but not limited to color, quantity, size, express shipping, etc.) against datasetB (company product price list compiled according to the above-mentioned parameters and marked with Black suit, 2 pieces, L size, and Yes for express shipping) from left to right, and kind of like the Plinko simulation (however based on the state of the parameters including color, quantity, size, express shipping, rather than based on probability) from top to bottom of the company product price list.

The expected result should be
sale 001: black suit + 2 pieces + L size + normal shipping = combo A, price * quantity = total price
sale 002: white suit + 3 pieces + M size + express shipping = combo B, price * quantity = total price
sale 003: blue suit + 2 pieces + S size + express shipping = combo C, price * quantity = total price

What Alteryx formulas/tools could I use to establish the workflow?

[TUSHAR050392]

Hey @tsao88 Can you provide some sample data to see how your data looks?

[CoG]

I agree with @TUSHAR050392 , data (in the form of a file or text, not images) would be helpful.

That being said, it sounds like the Join Tool is going to be beneficial for this use case (If you are working with ranges instead of single values, for quantity as an example, you may need to work some extra Alteryx magic), but ultimately, the Join Tool should be key.

Happy Solving!

[tsao88]

Dataset A -Sales Records
Sales Today	Ordered by Customer ID	Color	Size	Express Shipping	Quantity
1	111	Brown	L	Y	1
2	222	Black	XL	N	2
3	333	Blue	M	Y	2
4	444	Black	M	N	1
5	555	White	S	Y	1
6	666	Brown	L	N	3
7	777	Blue	L	Y	1
8	888	Blue	XXL	N	2
9	999	Black	M	Y	1
10	1010	Purple	S	N	6
Dataset B - Company Price List
Product ID	Suit Color	Size	Express Shipping	Combo	Price per item
100	Brown	L	Y or N	A	29.99
200	Black	XL	Y or N	B	35.99
300	Blue	XXL	N	C	46.58
400	White	M	N	D	52.36
500	Any other color	Any size	Y or N	E	49.99
Desired Results from Alteryx
Sales Today	Ordered by Customer ID	Combo	Price	Grand total
1	111	A	29.99	29.99
2	222	B	35.99	71.98
3	333	E	49.99	99.98
4	444	E	49.99	49.99
5	555	E	49.99	49.99
6	666	A	29.99	89.97
7	777	E	49.99	49.99
8	888	C	46.58	93.16
9	999	E	49.99	49.99
10	1010	E	49.99	299.94

[tsao88]

Dataset A -Sales Records
Sales Today	Ordered by Customer ID	Color	Size	Express Shipping	Quantity
1	111	Brown	L	Y	1
2	222	Black	XL	N	2
3	333	Blue	M	Y	2
4	444	Black	M	N	1
5	555	White	S	Y	1
6	666	Brown	L	N	3
7	777	Blue	L	Y	1
8	888	Blue	XXL	N	2
9	999	Black	M	Y	1
10	1010	Purple	S	N	6
Dataset B - Company Price List
Product ID	Suit Color	Size	Express Shipping	Combo	Price per item	Max price if buying 6 or more
100	Brown	L	Y or N	A	29.99	N/A
200	Black	XL	Y or N	B	35.99	N/A
300	Blue	XXL	N	C	46.58	N/A
400	White	M	N	D	52.36	N/A
500	Any other color	Any size	Y or N	E	49.99	250.00
Desired Results from Alteryx
Sales Today	Ordered by Customer ID	Combo	Price	Subtotal	Grand Total
1	111	A	29.99	29.99	29.99
2	222	B	35.99	71.98	71.98
3	333	E	49.99	99.98	199.96
4	444	E	49.99	49.99	49.99
5	555	E	49.99	49.99	49.99
6	666	A	29.99	89.97	89.97
7	777	E	49.99	49.99	49.99
8	888	C	46.58	93.16	186.32
9	999	E	49.99	49.99	49.99
10	1010	E	49.99	299.94	250.00

[CoG]

I made one adjustment to Dataset B (creating separate records for each unique state). Here is a workflow that Joins the Price Catalog to Order Table (and then appends default pricing info for all un-joined records):

[OTrieger]

@tsao88 

I guess the real question will be, based on your Alteryx knowledge what will you do? Which tools will you use to get there.

The idea here is to assist you getting it solved not do to the work for you. I keep see that malady of people posting created workflows while they do not know if you did anything to try to solve your issue. Solving other people problem is wrong, as it leads to laziness and to a set of mind that the minute that someone facing an issue he or she run to post it with the hope that someone else will solve it for him, thus killing the real development of the person that on automatic basis will ask help each time that he will face some difficulties. A real Pro will try and find millions ways how to do it, he will not know everything and might need some assistance however he will have in place some thing in progress.

Being a Pro is a hard work as it demands to be able to solve problems, and they manage to figure out ways to get there by learning.

Always ask yourself what I can do about it! That will bring you one step closer to the resolution.