Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Apply formula's column rules to compare with dynamically added columns

Anzrey
7 - Meteor

Anzrey_0-1621052972253.png

I have a set of data that consist of multiple different types of product's price for each different markets, MarketA belongs to me and I conditionally formatted it to color code it red when a product's price in our market is higher than every other market's product. Using column rules in table tool, this logic is formed

 

IF toNumber([MarketB])==0 && toNumber([MarketC])==0 && toNumber([MarketD])==0 && toNumber([MarketE])==0 && toNumber([MarketF])==0 && toNumber([MarketG])==0 && toNumber([MarketH])==0 && toNumber([MarketI])==0 && toNumber([MarketJ])==0 then "background-color :lime" elseif toNumber([MarketA (ours)]) > toNumber([MarketB]) && toNumber([MarketA (ours)]) > toNumber([MarketC]) && toNumber([MarketA (ours)]) > toNumber([MarketC]) && toNumber([MarketA (ours)]) > toNumber([MarketD]) && toNumber([MarketA (ours)]) > toNumber([MarketE]) && toNumber([MarketA (ours)]) > toNumber([MarketF]) && toNumber([MarketA (ours)]) > toNumber([MarketG]) && toNumber([MarketA (ours)]) > toNumber([MarketH]) && toNumber([MarketA (ours)]) > toNumber([MarketI]) && toNumber([MarketA (ours)]) > toNumber([MarketJ]) then "background-color:red" else "background-color : lime" endif

 

 

Here is what I want to achieve, since using that logic each time a new market (column) is added into the data set, the code have to be formatted again to add in the new market (column) manually, so I was thinking if there is a way to compare our column (MarketA) with unknown or dynamically added columns instead of manually adding new lines of code each time a new market (column) is added in 

attached is the sample workflow for this conditional formatting.

5 REPLIES 5
shreyanshrathod
11 - Bolide

Hi @Anzrey ,

 

Not sure if this the optimized and the best way to achieve desired results, but it works fine!!

Have a look at it and let me know if it helps.

 

I have tested by adding several columns, deleting existing columns and by changing prices and it works perfect in all scenarios.

Give it a go!!!

 

Regards,

Shreyansh Rathod

atcodedog05
22 - Nova
22 - Nova

Hi @Anzrey 

 

Here is how you can do it.

atcodedog05_0-1621069951163.png

 

 

1. whenever we want to apply logic on multiple variable fields we would need a transpose tool. Here in transpose tool i am keeping Product & MarketA as key and all other columns will be converted to row and by selecting dynamic or unknown columns it converts new columns also into rows (i,e new markets also into rows)

atcodedog05_1-1621069253555.png

2. As i could see there was a distinct formula that you want to color background red when all markets are lesser then market A. So in the formula i am creating a red column checking whether a market is less than market A if yes assign value 1 else 0. 

3. In summarize tool i am grouping by Product & MarketA (key columns), taking sum of red, count of red and sum of value.

4. In the formula i am creating column color to check if sum of value is zero or null then green (1st condition), sum of red and count of red is same then MarketA is greater than all markets then red (2nd condition) else green (else condition)

5. I am using a join tool tool get all other columns back.

6. In table tool i am setting formatting formula as color for Market A.

 

Hope this helps 🙂

Anzrey
7 - Meteor

Great solutions @atcodedog05 and @shreyanshrathod thanks for helping out! 
both solution seems to work out fine 

atcodedog05
22 - Nova
22 - Nova

Hi @Anzrey 

 

Glad to hear both worked out for you 🙂 you are allowed to mark multiple post as solution.

shreyanshrathod
11 - Bolide

@Anzrey ,

 

One of the trickiest problem that I have encountered on Discussions forum.

Glad that it could help you out. 

 

Regards,

Shreyansh Rathod

Labels