We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Table Tool - Dynamic Formatting

ghiggins
7 - Meteor

I have the below sample data set except it has a few hundred columns and thousands of rows.  I want to export to excel and in this example highlight the value 10 because in the other column it had a yes value.  I can create a formula in the table tool ( [Amount - Correct] = "Yes" ) but I would have to do that for hundreds of columns.  I tried making a formula in the unknown/dynamic field but I couldn't find a variable that would use the current field name plus a prefix or suffix.  

 

Let me know if you need more information.  The purpose of the output is comparing two files and the yes/no indicates if the values matched in the two files.

 

 

ghiggins_1-1594156680906.png

 

 

 

5 REPLIES 5
MichaelLaRose
10 - Fireball

HI @ghiggins 

 

You could create a data stream that pivoted the data and assigned the value of 1 for yes values and 0 for nos.

 

After that just summarize the data (Max of the new field) so that you have a 1/0 flag for if the the record should be highlighted.

 

Once you have a unique value for each record you can join the data and you would have a single field to check in your query.

 

Does that make sense?

 

Best,

Michael

ghiggins
7 - Meteor

Hi @MichaelLaRose ,

 

Are you saying to transpose the data so I have 1 column of data and then 1 column with the 1/0 flag?  Then combine the two columns and cross tab it back.  I could then use a dynamic formula across all my columns.  

 

This was a solution I thought of as well.  Biggest thing I was worried about is formatting.  I did a find replace to get rid of the flag in excel which worked but it seemed to remove leading zeros and I was worried what other issues it could cause.

 

Let me know if this was your thinking.  Thank you for the help!!

MichaelLaRose
10 - Fireball

No.

 

After you create the 1/0 flag use the summarize tool grouped by only what makes the records in the final dataset unique (aggregate across the transposed fields).

 

This will result in a second dataset that has the same number of records as your original dataset but instead of a large number of "Yes"/"No" flags you have a single "Is there a reason to highlight" flag.

 

Once you have those two datasets just join the two of them and keep only your overall flag.

 

Note: you can probably do this with a "Yes"/"No" flag instead of a 1/0 by taking the minimum of the string field if you want to show that there is at least 1 yes in the flags.

 

ghiggins
7 - Meteor

Hi @MichaelLaRose 

 

I really appreciate the help.  If I am reading correct I would summarize to get if each record should be highlighted.  This would flag each record, but I want to be able to flag and highlight each individual cell.  

 

Below is what I got after summarizing.  

 

ghiggins_0-1594226405452.png

 

MichaelLaRose
10 - Fireball

HI @ghiggins 

 

I'm sorry, I didn't realize what you goal was.

 

I don't know any way to get around the multiple calculation requirement to flag individual cells in Alteryx. However, if you are writing to Excel you can append into a worksheet with conditional formatting in order to basically achieve the effect you're going for.

 

The Excel sheet is currently set up just save them to the same folder and run. The conditional formatting is specifically looking for "Yes" in each cell and reformats the cell to red if it is a "Yes"

 

Thoughts,

Michael

Labels