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

Loop through columns

Chirag_Gandhi07
8 - Asteroid

Hi everyone,

 

I have attached a sample excel file that contains emails and titles as columns. I want to break this down to only display two records each which can be done with the recordID tool and the filter tool. The issue is that when I output them to only display two records each, they show all the columns in the dataset. How do I make it so that only the columns with a value for each email appears? Any help would be greatly appreciated.

 

Thanks!

16 REPLIES 16
AmeliaG
Alteryx
Alteryx

Hi @Chirag_Gandhi07,

 

It looks like it would be great to add a Transpose and Filter tool. First, Transpose the data with the 'Email' field as the Key and then all other columns as the Data. Then, use the 'Filter' tool to filter out values where the cell is blank or null. The resultant will be one email column, one title column and one value column. 

 

Does this meet your needs?

 

Thanks!

 

Amelia

Chirag_Gandhi07
8 - Asteroid

@AmeliaG Thanks for your response. I have attached a sample workflow and excel file. So if I output only record 1, I want the only column to be displayed Title 2 because that column has a value for test1@test.com. Not sure how to go about doing this though.

AmeliaG
Alteryx
Alteryx

Hi @Chirag_Gandhi07,

 

Do you plan to have all of the records in one output? If so, it would not be possible to have only one column per row since the columns are named differently. If I am misunderstanding, can you please show me what you would like the output to look like?

 

I've attached an example here which would make one column for title and one column for the value. The output looks like this:

 

output.png

 

Thanks,

 

Amelia

Chirag_Gandhi07
8 - Asteroid

@AmeliaG So I have attached an example of what my output should look like. The first table is my current output data and the second table is how I would like it to be displayed. I took the first four records only just as an example, but you can see that only the columns that pertain to the four emails appear as outputs.

AmeliaG
Alteryx
Alteryx

Hi @Chirag_Gandhi07,

 

To me, it looks like your desired output is the same as the current output but with fewer records. Can you please help me understand the difference?

 

Amelia

Chirag_Gandhi07
8 - Asteroid

@AmeliaG So this is just a sample but my real workflow has 3668 columns and 3589 records. So, I want to take 150 records each and display only the columns that have a value in it for those particular 150 users. So for example, one user may have a value in Title 35, Title 89, and Title 3600. Rather than displaying all 3668 columns for the subset of 150 users, I only want the output to display the columns that pertain to those users. So let's just say I filter to display only one record, the user mentioned above. Rather than displaying all 3668 columns, the output should only display Title 35, 89, and 3600. Does that make better sense?

AmeliaG
Alteryx
Alteryx

Hi @Chirag_Gandhi07,

 

Thanks for the explanation, this makes much more sense. You need to use a batch macro. Each 150 records will represent 1 batch and one output file will be created per batch with only the columns needed for those 150 records. I've attached the example workflow including the batch macro which will take care of this. 

 

Hope this helps!

 

Amelia

Chirag_Gandhi07
8 - Asteroid

@AmeliaG Thank you so much! Unfortunately, I can't open the workflow you sent because I am using Alteryx Designer version 11.0. Do you know how I could get the workflow to open?

AmeliaG
Alteryx
Alteryx

Hi @Chirag_Gandhi07,

 

I've attachted the workflow and batch macro to work with 11.0. 

 

Let me know how it goes!

 

Amelia

Labels