Flatten Columns for Chart Visualization

[marc_aguilar]

Hello,

I am looking for AppSheet ninja ⚔️ that knows how to solve this.

I have a table with 1 column with Player, 1 column with Character, and 3 columns with Items (of the character).

I want to display a chart that fetch all the items selected in the 3 columns in order to visualize the frequency of each item via `aggregate piechart`.

Constraint:

• The source has security filter so the gamer1 will only see their data and gamer2 will only see their data.

In google sheets will be looking like this, but in Appsheet I do not know how to perform it.

Thanks in advance.

[WillowMobileSys]

The issue causing you trouble is that your "raw data" is in "non-normal" form or more specifically - you are listing Items as separate columns.  AppSheet, and most any other system, is a row-based processing system.  It does not support column functions to perform counts.  

You want to redefine your raw data table to have a single Item column and then split each current row shown into 3 separate rows.  Then you can use the proper chart - most likely the Histogram chart which, if I remember correctly will give you a bar for each item and adjust the bar based on the count of item rows.

[marc_aguilar]

Hello @WillowMobileSys , thanks for your quickly response. Based on your comment am I right guessing it's not possible to do kind of transformation inside the same appsheet then?

My raw data schema cannot be redefined as it will highly affect the app behaviour and UX.

[WillowMobileSys]

No there isn't any built in transformation or transpose type functions. It is expected to build the data in "normal" form which avoids any need for transposition.

I do strongly encourage to update the app data structure.  It could constantly cause implementation issues being in a non-normal form BUT that does depend on how frequently the 'raw data" is utilized throughout the app. 

If it is necessary to keep the current data design, then you can work around the problem for your chart by creating another table  that is updated whenever entries or edits are made to the raw data.  Just be warned that anytime you need some specific organization of the "raw data" in its current state, you will likely need to implement another workaround.

To proceed with this work around...

Create a table exactly like your "expected" table shown.  Then add an action set attached the Form Save behavior of the "raw data" Form view.  The action set will inspect the "expected" table and if a row for that item is NOT present, it will insert one.  If the row IS present then the count is bumped by whatever count is necessary for the new/updated "raw data" row.  NOTE:  you will likely need to use the INPUT() function to pass values into the update function.

Once you have the action set created and attached to the "raw data" Form view, the "expected" table can be used in your chart directly.

I hope this helps!

[marc_aguilar · Accepted solution]

Thanks again for all your inputs, much appreciated.

At the end I could replicate my need by having another table in GoogleSheets, with my expected table using formulas.

Flattening all my data and also keeping the Player name for the Data Security Filters.

FORMULA

ArrayFormula( SPLIT( FLATTEN('GCloud Community'!$A$4:$A$9&","&'GCloud Community'!$C$4:$E$9), ","))

RESULT

This allowed me to create the charts with the user item entries (for the 3 cols).

[Arni_Kli]

@marc_aguilar , you can try to create new read-only table in Googlesheets by using a QUERY() function and try to get the necessary data set for your chart. Moreover, it will give you the opportunity to bypass the constraint of secutity filter and show this chart to all gamers (if I understand correctly what you want).

[marc_aguilar]

Thanks @Arni_Kli for your answer, this inspired me to use another tab, with formulas (others than QUERY() ) that allows me having the table structure I need.