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.
SOLVED

Using Table tool then Cross tab

hal_dal
8 - Asteroid

Hello,

 

I'm facing a challenge and wanted to see if I could get some help.

I have a scenario where I need to highlight fields/columns that have been transposed and I then need to use cross-tab to bring them back to its original format.

However, when I try to do that, the Table tool only gives me back "Table" as the value when I try to use Cross-tab.

 

and I was wondering if anyone could help me solve this issue?

 

Thanks

6 REPLIES 6
CharlieS
17 - Castor
17 - Castor

Hi @hal_dal 

 

When data goes into a Table tool, it is built as a single field which means the standard transformation tools will not work. What you might need to do is use the data ahead of time to determine what needs to be highlighted, create some flags for those fields, then use the Table tool to create the final table and use those flags to apply the formatting you desire. 

 

Could you share some screenshots/sample data to give us a better idea of what you'd like to do? I'm sure we could figure something out. 

hal_dal
8 - Asteroid

@CharlieS  Thank you for your response!

Basically, I have two data files that I need to identify whether any columns have changed and if any fields have changed and I have done that using transpose tools.

 

File 1:

 

First NameLast NameDOBMarriage StatusAddressCityPostal CodeCountrySalary
BobMartin1/1/2000Single123 StreetMississaugaL5A3K8Canada68,000
JoshWhites11/11/1994Married3127 Lolita GardensMississaugaL1G8B4Canada100,000
PatriciaAl-Siddiqui10/12/1980Widowed123 DriveMississaugaM7A1J8Canada90,000
HudaDallal12/12/1999Single3333 DriveOshawaL1H8J8Canada

34,000

 

 

 

 

File 2:

First NameLast NameDOBMarriage StatusAddressCityPostal CodeCountrySalary
BobMartin1/1/2000Married123 StreetTorontoL5A3K8Canada68,500
JoshWhites11/11/1994Married3127 Lolita GardensMississaugaL1G8B4Canada100,000
PatriciaAl-Siddiqui10/12/1980Widowed123 DriveMississaugaM7A1J8Canada90,000
HudaBobs12/12/1999Single1222 StreetOshawaLJ9L9KCanada55,000

 

 

Basically,

 

I need to compare whether there's been any changes between File 1 and File 2 and highlight the changes (e.g., changed from Single to Married, I need to highlight Married in File 2 as a change)

 

 

thanks a lot for the help!

CharlieS
17 - Castor
17 - Castor

Sure thing, @hal_dal 

 

I've put together a quick example that shows how a flag method could work. It identifies rows where a change occurs and adds a flag field to both data streams. Now when those layout fields are created by the Table tools, the flag field can be used to update formatting by changing the row color, but not actually included in the data. This is done by de-selected the "Flag" field and adding a "Row Rule" to change the background color based on the value of that field.

 

Check it out and let me know what you think. 

hal_dal
8 - Asteroid

Hi @CharlieS 

 

I'm looking to highlight speciifcally which record (specific field) has changed and not to highlight the entire row.

Sorry for the confusion:

 

e.g., the marital status from file 2 changed from Single to Married

then I would highlight that record that says Married rather than the whole row.

 

Hopefully that's not too confusing, thank you

CharlieS
17 - Castor
17 - Castor

I understand. This is possible, but it gets a bit more complicated. I'll explain the method below and I've got an example attached that shows how it's done. 

 

The first part is the same: transpose the data to identify the vales that should be highlighted. Instead of summarizing this by rows, we could summarize this into a set of Column Rules for every field that specifies which RecordID values from that field to apply the formatting to. For example: the [Marriage Status] field would have a Column Rule that applies when RecordID=1. To update these Column Rules with data from the workflow, we'll have to wrap the Table tool in a batch macro so we can use the Control Parameter to edit the XML of the Table tool. 

 

Tricky parts:

- To get the rules formatted the way I want, I use a Table tool and apply the formatting in the tool. I then open the XML of that tool and copy the style rule section so I can modify it like you see in the formula below. To view tool XML: enable XML view in your user settings (Options > Edit User Settings > Advanced > Display XML in Properties Window), click on the tool, then click on the "</>" icon on the configuration window. 

- Each table style rule gets a UUID assigned to keep things organized inside the Table tool. Luckily the function UuidCreate() is available to call the same process when creating your own rules outside of the Table tool. Here's the formula I used to create each Column Rule:

 

' <Rule Name="'+UuidCreate()+'">
<RowRule value="False" />
<HighPrecedence value="False" />
<Field>'+[Name]+'</Field>
<Test>Formula</Test>
<ApplyTo>Data</ApplyTo>
<SimpleVar>RecordID</SimpleVar>
<SimpleOp>==</SimpleOp>
<SimpleConst>-1</SimpleConst>
<Formula>[RecordID] in ('+[Concat_RecordID]+')</Formula>
<Style BackColor="#ffff80" />
<PrettyName>Highlighting Rule</PrettyName>
</Rule>'

 

- The Action tool settings are critically important to apply the XML update correctly. I select the "StyleRules" parent and set "Update Inner XML". Since the "StyleRules" parent is always present, I just need to populate contents within.

 

20200709-DynamicTableStyleRules.PNG

 

Let me know if you have any questions. 

 

EDIT: I added a few tools to make sure everything works in the case where there are no differences between the tables. (Basically just sends an empty rule set)

ghiggins
7 - Meteor

Hi Charlie,

 

This is a great solution!  Do you know any articles on how to do this sort of thing with the XML?  I am trying to do some thing similar.  I have a lot of columns each with a corresponding column that designates the conditional formatting.  For example, column one "sales" column two "sales_formatting".  Is there a way to highlight my cells based on the corresponding column without having to write formulas for each column?

 

Thank you,

 

Garrett Higgins

Labels