Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to Convert Rows to Column from single column having multiple rows.

Abhi27101990
6 - Meteoroid

I am new to this platform and i am facing an issue so need help. My problem scenario is I have File A which has multiple rows in one column and these rows are actually headings which will be used as a column for extracting column details from file B. For example:

File A                                                  

Company Name (this is column heading )

CapIQ Excel ID 
Ticker
Description
HQ (City)
HQ (State)
HQ (City)

File B

Company Name CapIQ Excel ID    Business Description     Revenue     EBITDA    EBIT    HQ (City)

Company A         IQ1919                   xxxxxxxxxx                        $700           $500         $350      US

 

I want to create a new file in which the column names will be of FILE A and the data will  be extract from FILE B only for column mentioned in FILE A.

 

 

How can this be achieved? Any help?

6 REPLIES 6
Rihan_Mohamed
8 - Asteroid

Created a workflow for the same, You can use the attached workflow and let me know any more issues.?

zajaccount
9 - Comet

Hi,

 

you could do this by doing the following:

 

zajaccount_2-1588621682467.png

 


(the top file includes the data in the column, the bottom is the "lookup" file.

 

You could:

 

1. Use the data cleanse to make sure there are no unnecessary characters in the rows (such as leading/trailing spaces)

2. Add a record ID

3. Cross tab the column using the following configuration:

 

zajaccount_1-1588621547947.png

where field 1 is the column that contains the headers

 

4. Use the dynamic rename to get rid of the _ separators in column names (the corsstab tool produces the column names with spaces replaced by _. The reverse this, use the dynamic rename with the following expression:

 

replace([_CurrentField_],"_"," ")

 

4. Use the sample to skip 1st row.

5. Union the files by name 

 

Please find the flow attached - let me know whether this works for you.

 

 

zajaccount
9 - Comet

Alternatively, for a simpler solution, use the make columns tool:

 

zajaccount_0-1588621900073.png

and a dynamic rename set to take field names from first row of data.

Abhi27101990
6 - Meteoroid

Thank you very much for the solution. Now i will apply union on File A and File B to extract the relevant column data.

Abhi27101990
6 - Meteoroid

Using the workflow financial data like Revenue, EBITDA and EBIT are showing under Description, HQ City, HQ State and US country under ticker which is incorrect.

Rihan_Mohamed
8 - Asteroid

I have updated the workflow, Attached is the screenshot of the workflow and result.

Rihan_Mohamed_0-1588707345113.png

Rihan_Mohamed_1-1588707369578.png

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

 

 

Labels