LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ahines

Good Evening, Everyone! I am working on a another metric filled KPI report and was about to start trying what I imagined to be a very tedious process of copying and pasting nodes and rewriting the innards, decided I would ask the forum if there was a "work smarter, not harder" solution.

This report has 13 columns of data (Data types ranging from integers, to string-type- percents, to currency), and what I need to do is simply sort every column and add a column next to it with a ranking [1-?]. This ranking would take into account duplicate values and keep the ranking the same for all the lines that are the save value. So theoretically at the end I'd have 26 columns of data. Examples to follow. This doesn't seem very hard in theory, and I've seen very little on the subject which makes me believe it must be a simple solution. I would also be interested to see how it is accomplished without the fun creation of the "fan" of nodes, each repeating the same single-step process with slight changes.


Example:

Percent 1 || Percent 1 Rank

0.001% 1
0.002% 2
0.003% 3
0.003% 3
0.004% 4



BONUS Points to anyone that can tell me how to format numbers for percents and currency without making them strings and concat with $ and %.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Hi, Andrew. So the way to do this would be to sort the percentages, and then assign a rank that increments when there's no tie with the previous value.

As for number formats, any sort of numerical operation needs to be done on numerical data types - integers, doubles, etc. You can't have %'s and $'s in numerical data types. If you need those in your results, I would just add them back in at the very end.

By the way, you may also find this post helpful for a variation on the ranking process (the above would become 1, 2, 3, 3, 5). https://infogix.zendesk.com/hc/en-us/community/posts/360050682614
Attachments:
getRank.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ahines

Hi, Roger! Thank you for coming to my rescue! Out of curiosity, is there a way to do this dynamically in a single node (sort+rank for 13 columns)? I see how to manipulate this for all my variables simply enough, but the shear amount of nodes necessary because of the partnership between sort/rank makes me wonder if there is a way combine all this some way.

I actually did read the dense ranking forum entry, but I couldn't figure out how to get around the grouping that you did.

The number formats were a bit of a pickle- have you ever seen or heard of an issue involving an excel publish document that when you attempt to format the columns in excel they format in an unexpected way? As an example, the one I created with this graph I am working on required me to change certain columns to currency (like we spoke about above), but when I hit the accounting button it instead put everything in comma style. The reverse occurred when I attempted to put anything into comma style, it put it into currency format. I thought it may be linked to the data type, but they are just doubles and strings. Even when converted inside of excel to numbers, they still perform the same way. It is incredibly odd.

Thanks again for you help, Sir-

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Ok, so you could do this dynamically, but like most problems, there's a hard way and an easy way. I've attached the easy way. This would work for "small" data sets (let's say ~15 columns and ~50,000 records*). The reason is that it pivots the fields that you want to rank. So say you have 100 records and 13 fields you want to rank, it would expand the data to 1300 (13 times 100). The pivot node takes in a pattern. In my case, I'm ranking any field that starts with 'Val'.

In terms of the Excel formatting issue that you're seeing, I haven't seen that. I just published out some doubles (numbers with decimals) to an Excel file and was able to change it to 'Accounting' type fine in Excel.

* I included a larger sample data with 10k records that you can test for yourself.
Attachments:
getRankAcrossFields.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ahines

Hi, Roger- Sorry it took so long to get back to you but duty called! So, I think I can get the previous (first) example to work in my data, but I have a quick question about modifying the code to work. So I decided to go with leaving the data in double form rather than modifying it as a string to visually be a percent. So all the data exists in the input and nothing needs changed- but your ranking filter logic is based on the Pct variable you named before. My question is this, and simple, I think: Can I use a string as a variable? My names are written as (Voids % to Sales)- should I modify all 26 to something easier and then rename them after the fact, or is there an easier way to call and name strings as variables that I am not aware of? Of course this is all in the name of expediency, so I may just be trying to be too lazy

In the dynamic option you gave me, I have no idea how to modify that to fix my data set in it's xlsx/xls format, so I think I will stick the former. Thanks a bunch!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Ok, so in this new version, I've stuck the logic into a composite. Now you can open the composite and select the field you want to rank.
Attachments:
getRank_2.brg