Merge Vendor Fields and Sum

[hellyars]

Help. I want to sum by Vendor across Quantity Range (1-99, 100-499, 500-1000).  Basically 1 column for each field (instead of 3 for Vendor).  Notice how the Vendor can change as the quantity increases.

Thanks

Part	Vendor	1-99	Vendor	100-499	Vendor	500-1000
13242314	ACME	$20	ACME	$15	ACME	$10
14212412	SPECIALIZED	$150	SPECIALIZED	$125	CERVELO	$75
q4623523	COMPANY-A	$300	COMPANY-B	$200	ACME	$100

UPDATE....

Desired output

Vendor	1-99	100-499	500-1000

[AngelosPachis · Accepted solution]

Hi @hellyars ,

One way r=to do that would be with the following workflow

Essentially you are pivoting your data multiple times to bring the table in the proper structure which will allow you to aggregate the values for each vendor and make it as dynamic as possible.

Hope that helps.

Regards,

Angelos

[Qiu · Accepted solution]

@hellyars 
Figure you would need the Part Field

[hellyars]

@AngelosPachis I've never used the CEIL function before, it will come in handy.  Thanks!

[Tyro_abc]

Hi 

I used simple select and union to achieve this.

Best Regards

[hellyars]

@AngelosPachis  I ran into a real world problem.  The Group expression stopped working part way through a workflow and started assigning null() at would have been Group 256.

This is where it stops working.

[AngelosPachis]

Oops, just saw this @hellyars ,

I've replied to the other post you have created cause I saw that first.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/CEIL-RecordID-2-Stops-Working-at-Recor...

The issue is probably the data type of the Field "Group". That's probably set to be Byte, but if you change it to an Integer (Int16,32 or 64) that should do it.

Hope that helps.