Hi team, I am learning Alteryx. I required your help to rearrange following table in 2 formats Raw Data RICbooksecurityBalanceSystemQuantityAAA110,000.00Apple10AAA28,000.00Apple8AAA361,000.00Apple61AAA45,000.00Apple5AAA5-1,500.00Apple2AAB6-50,000.00Andoid-61AAB7-9,900.00Andoid-10AAC8-5,000.00Andoid-5AAC9-7,900.00Andoid-8AAD101,450.00Andoid-2AA Total 11,150.00 ABA1181,000.00Apple81ABA1256,000.00Apple56ABB13-72,000.00Andoid-81ABC14-56,000.00Andoid-56AB Total 9,000.00 ACE153,000,000.00Apple3000ACE161,500,000.00Apple1500ACB17-2,900,000.00Andoid-3000ACC18-1,500,500.00Andoid-1500AC Total 99,500.00 Required Format 1:- RICbooksecurityPriceSystemQuantityAAA110,000.00Apple10AAB7-9,900.00Andoid-10AAA28,000.00Apple8AAC9-7,900.00Andoid-8AAA361,000.00Apple61AAB6-50,000.00Andoid-61AAA45,000.00Apple5AAC8-5,000.00Andoid-5AAA5-1,500.00Apple2AAD101,450.00Andoid-2AA Total 11,150.00 ABA1181,000.00Apple81ABB13-72,000.00Andoid-81ABA1256,000.00Apple56ABC14-56,000.00Andoid-56AB Total 9,000.00 ACE153,000,000.00Apple3000ACB17-2,900,000.00Andoid-3000ACE161,500,000.00Apple1500ACC18-1,500,500.00Andoid-1500AC Total 99,500.00 Required Format2:- In this I want to remove those who are almost matching in opposite direction (Threshold of 1000) RICbooksecurityPriceSystemQuantityAAA361,000.00Apple61AAB6-50,000.00Andoid-61AA Total 11,000.00 ABA1181,000.00Apple81ABB13-72,000.00Andoid-81AB Total 9,000.00 ACE153,000,000.00Apple3000ACB17-2,900,000.00Andoid-3000AC Total 100,000.00 Please let me know if its possible to deriv such result from Alteryx. Regards, Ashish

Rearrange of Data

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

Datasets

Reporting

Output

Predictive Analysis

Interface Tools

Text Mining

Documentation

Accepted answers

Qiu

@AshishAgarwal
Hope this is what you need.

0520-AshishAgarwal.yxmd

All comments

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:

Rearrange data.yxmd

Qiu

@AshishAgarwal
Hope this is what you need.

0520-AshishAgarwal.yxmd

AshishAgarwal

Hi Qui,

Thank you so much.. Its very helpful.

Agreed to your solution.

Regards,

Ashish

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703