Search code examples
alteryx

How to flatten a 2D data into 1D in Alteryx

I am a newbie to Alteryx and trying to solve a rather difficult problem. My data is not only unclean but also extremely entangled (example below):

enter image description here

I want to transform it to a flattened format which can be used to run aggregation and other sorts of analysis. Following is the output:

enter image description here

How to go about it?

Solution

  • Don't have Alteryx at hand,

    1. Rename last 8 of 9 columns. Example:

    Column B to

    "Company A,2018,Shareholding"

    COlumn C to

    "Company A, 2018, percentage"

    1. Then use a Sample Tool to split your records to two flows, Individuals, Companies (Union them back in the final step), then a Formula Tool to specify each part as "individual promoters" or "Companies" will be handy

    Do below for both parts:

    1. use a Transpose Tool (choose 8 renamed columns as "data columns", the Formula tool above can be "key columns"), therefore, company, year, shop name are in a horizon place, you get Name/Value pair:

    "Individual Promoters","Company A, 2018, Shop1, ShareHolding", 250,000

    "Individual Promoters","Company A, 2018, Shop1, Percentage", 0.000273

    1. use text to columns, comma as separator, you get 4 columns needed.

    2. Use a Formula tool followed by a filter Tool, to split "ShareHolding" and "Percentage" to two flows

    3. Use Join (3 join keys: company, year, shopname) to join them back, you now get:

    Company A, 2018, Shop1, 250,000,0.000273

    1. Use aforementioned Union to combine "individual promoters" and "companies" back to 1 flow.

    Done