Rearrange of Data

[AshishAgarwal]

Hi team,

I am learning Alteryx.

I required your help to rearrange following table in 2 formats

Raw Data

RIC	book	security	Balance	System	Quantity
AA	A	1	10,000.00	Apple	10
AA	A	2	8,000.00	Apple	8
AA	A	3	61,000.00	Apple	61
AA	A	4	5,000.00	Apple	5
AA	A	5	-1,500.00	Apple	2
AA	B	6	-50,000.00	Andoid	-61
AA	B	7	-9,900.00	Andoid	-10
AA	C	8	-5,000.00	Andoid	-5
AA	C	9	-7,900.00	Andoid	-8
AA	D	10	1,450.00	Andoid	-2
AA Total			11,150.00
AB	A	11	81,000.00	Apple	81
AB	A	12	56,000.00	Apple	56
AB	B	13	-72,000.00	Andoid	-81
AB	C	14	-56,000.00	Andoid	-56
AB Total			9,000.00
AC	E	15	3,000,000.00	Apple	3000
AC	E	16	1,500,000.00	Apple	1500
AC	B	17	-2,900,000.00	Andoid	-3000
AC	C	18	-1,500,500.00	Andoid	-1500
AC Total			99,500.00

Required Format 1:-

RIC	book	security	Price	System	Quantity
AA	A	1	10,000.00	Apple	10
AA	B	7	-9,900.00	Andoid	-10
AA	A	2	8,000.00	Apple	8
AA	C	9	-7,900.00	Andoid	-8
AA	A	3	61,000.00	Apple	61
AA	B	6	-50,000.00	Andoid	-61
AA	A	4	5,000.00	Apple	5
AA	C	8	-5,000.00	Andoid	-5
AA	A	5	-1,500.00	Apple	2
AA	D	10	1,450.00	Andoid	-2
AA Total			11,150.00
AB	A	11	81,000.00	Apple	81
AB	B	13	-72,000.00	Andoid	-81
AB	A	12	56,000.00	Apple	56
AB	C	14	-56,000.00	Andoid	-56
AB Total			9,000.00
AC	E	15	3,000,000.00	Apple	3000
AC	B	17	-2,900,000.00	Andoid	-3000
AC	E	16	1,500,000.00	Apple	1500
AC	C	18	-1,500,500.00	Andoid	-1500
AC Total			99,500.00

Required Format2:- In this I want to remove those who are almost matching in opposite direction (Threshold of 1000)

RIC	book	security	Price	System	Quantity
AA	A	3	61,000.00	Apple	61
AA	B	6	-50,000.00	Andoid	-61
AA Total			11,000.00
AB	A	11	81,000.00	Apple	81
AB	B	13	-72,000.00	Andoid	-81
AB Total			9,000.00
AC	E	15	3,000,000.00	Apple	3000
AC	B	17	-2,900,000.00	Andoid	-3000
AC Total			100,000.00

Please let me know if its possible to deriv such result from Alteryx.

Regards,

Ashish

[DataNath]

How does this work? I can't really think of a way of getting the exact sort you're after, as applying ascending/descending sorts to any of the fields in the dataset wouldn't arrive at this - I think it'd have to be a brute force job. I can get you the expected outcome in step 2. If you want the first rearrange, the closest/best I can do for now is just what's left after the sort tool:

[Qiu · Accepted solution]

@AshishAgarwal 
Hope this is what you need.

[AshishAgarwal]

Hi Qui,

Thank you so much.. Its very helpful.

Agreed to your solution.

Regards,

Ashish