Loop through columns

[Chirag_Gandhi07]

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!

[AmeliaG]

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]

@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]

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:

Thanks,

Amelia

[Chirag_Gandhi07]

@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]

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]

@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]

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]

@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 · Accepted solution]

Hi @Chirag_Gandhi07,

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

Let me know how it goes!

Amelia