Using Table tool then Cross tab

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

Datasets

Regex

Macros

Date Time

Transformation

Accepted answers

CharlieS

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.

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)

20200709-DynamicTableStyleRules.yxzp

All comments

CharlieS

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

@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 Name	Last Name	DOB	Marriage Status	Address	City	Postal Code	Country	Salary
Bob	Martin	1/1/2000	Single	123 Street	Mississauga	L5A3K8	Canada	68,000
Josh	Whites	11/11/1994	Married	3127 Lolita Gardens	Mississauga	L1G8B4	Canada	100,000
Patricia	Al-Siddiqui	10/12/1980	Widowed	123 Drive	Mississauga	M7A1J8	Canada	90,000
Huda	Dallal	12/12/1999	Single	3333 Drive	Oshawa	L1H8J8	Canada	34,000

File 2:

First Name	Last Name	DOB	Marriage Status	Address	City	Postal Code	Country	Salary
Bob	Martin	1/1/2000	Married	123 Street	Toronto	L5A3K8	Canada	68,500
Josh	Whites	11/11/1994	Married	3127 Lolita Gardens	Mississauga	L1G8B4	Canada	100,000
Patricia	Al-Siddiqui	10/12/1980	Widowed	123 Drive	Mississauga	M7A1J8	Canada	90,000
Huda	Bobs	12/12/1999	Single	1222 Street	Oshawa	LJ9L9K	Canada	55,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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703