Table tool column rules being incorrectly applied?

[macjg]

Baffled by this one, hopefully something obvious I am doing wrong.

I pass in this data to the Table:

And this is what comes out:

Questions are:

1) Why does some data disappear? e.g. under "K9" the first row used to be -12 and now is blank

2) Why is my column rule to turn negative numbers red not being applied consistently?

It seems like the Table has memory from previous runs where the data under the same column headings was different and is applying the formatting according to the old data - is that possible?

Any help greatly appreciated - example workflow attached.

Thanks

[mceleavey]

Hi @macjg,

there's a bit of an "undocumented feature" on the table functionality when applying row level formulae.

If you use the actual "where" wizard and apply the change to the data type before you put it into the table ti works.

I've attached the solution for you. Let me know if that works.

[macjg]

Thanks for the reply mceleavey, but don't think that works either unfortunately.

The reason I was doing the type conversion in the row rule is that my data will have new column names come in all the time, and I wanted to be able to apply the formatting rules to these new fields.

When I try applying the "When" method to "Dynamic or Unknown Fields" in the table, I get the result:

"The field "*Unknown" is not contained in the record"

I avoided that issue when I use the "Formula" method with ToNumber([_CurrentField_])=0

but ended up with the mysteriously disappearing data instead - seems like a bug?

macjg

[mceleavey]

Hi @macjg,

In that case yes. If the columns are dynamic you'll need to raise it with Alteryx, unless one of the fountains of knowledge on here can help you out. 

I've come across this problem before and it seems to be a bug, but I also think it has something to do with mixed data types.

Interestingly, if you change the order in which the columns appear you can also get around it. Not always applicable I accept...

Sorry I can't be of more help.

Good luck.

M.

[macjg]

OK thanks anyway for trying.

Dear Alteryx - any chance of fixing this bug please?

Surprised more people have not raised this at it seems like pretty basic reporting functionality.

[SophiaF]

@macjg I agree we should look into this further - I have escalated this internally and we will post here with an update/request for more information. Thanks for reporting!

[SophiaF]

@macjg I can see why the data is disappearing - there are the following rules on the following columns:

Column	Rule	Condition	Rule Style
Z8	Z8 Rule 1	Z8 == 0	Replace with blank text (empty string)
H9	H9 Rule 2	H9 == 0	Replace with blank text (empty string)
F9	F9 Rule 1	F9 == 0	Replace with blank text (empty string)
K9	K9 Rule 1	K9 == 0	Replace with blank text (empty string)
Z9	Z9 Rule 1	Z9 == 1	Replace with blank text (empty string)
TOTAL	TOTAL Rule 1	TOTAL == 0	Replace with blank text (empty string)

I'm doing some more digging into why the Unknown option doesn't work.

[macjg]

Hi Sophia,

Thanks a lot for looking into this one.

You're correct that there are rules on the columns - that is intentional - but they don't seem to be being applied correctly.

e.g. the K9 rule to replace with blank text when data == 0; why does this rule get rid of the "-12" entry under K9 as in the screenshot on my original post?

Thanks,

John

[SophiaF]

@macjg - in the example workflow that was provided (where I got these rules) they do seem to apply correctly. Here's what I see:

Do you have a workflow with the same rules that you applied on your original post?

[macjg]

I've just downloaded the original workflow attached to my first post and run it again - I get the same results I originally posted - which bizarrely are different to yours!

However the "if negative - red" rule doesn't seem to be applied correctly in yours either.