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

Parse through each column and fill the value in the row

Batman_Rocks
5 - Atom

Hi Team,

I am new to the alteryx world. I am stuck with a problem , it would be really great if you can provide some pointers on this.

I need to create a work flow to get data in a particular format. Below is the requirement.

1)Check the column Product 1, Product 2, Product 3. . . for each row . If the value under the column is Y, then add a new row with the same details as the active row (in this case row 1) for the first three columns(i.e country, city, code in the sample data provided) .Create a new column in the output and copy the heading of the column for which the value is "Y" into the newly added column(In this  case "Products"). Continue this process for all the columns in row 1 until there are no more columns. Repeat the whole process for all the rows in the sheet . Sample data of how the output has to look is shown in table 2.

 

Actual Data

 

countryCitycodeProduct 1product 2Product 3
ChinaBeijingA123NY
IndiaBangaloreB123NYN
AustraliaSydneyC123YY

 

final data should like below.

 

countryCitycodeProducts
ChinaBeijingA123Product 1
ChinaBeijingA123Product 3
IndiaBangaloreB123Product 2
IndiaBangaloreB123Product 3
AustraliaSydneyC123Product 1
AustraliaSydneyC123Product 2
AustraliaSydneyC123Product 3

 

Thank you in advance.

5 REPLIES 5
Lisa_M
8 - Asteroid

I know this one!  If you use the Transpose tool, you should be able to compact your three product columns into one.  I would suggest do a find and replace on each of your product columns and replace Y with Product 1, etc, first. 

jdunkerley79
ACE Emeritus
ACE Emeritus

I'd suggest:

2019-02-13_10-35-16.jpg

 

Basically, transpose and filter

 

Sample attached

 

@Lisa_M beat me to it

chinu267
8 - Asteroid

Use the attached workflow by changing the source to your file and it should work

Batman_Rocks
5 - Atom

Thank you all:)

Worked like a charm. 

However when i transposed the data some of column headers in this case product 1 , Product 2 are not being displayed fully. 

I tried checking the size of the column but i it seems to be a very random issue. Some of the columns that are longer than the ones in that has issues are being displayed . Do you guys have any pointers around it?

 

Example: "IPVPN Cisco IOS IP SLA Enabled" is just being displayed as IPVPN where as  "IPv6 Enabled PoP (IPVPN, IPT/GID)" is being displayed completely. What am i missing here?
.

Batman_Rocks
5 - Atom

Hi all,

 

When i actually write it into the file, the whole column is visible and that serves my purpose.

Thank you all for the support:) 

Labels