hi, the data set i have has names across 3 different columns. i would like to consider that row of data if the name is included in one of those columns.
for example, names are included in columns a, b, and c.
if row 1 has names Rob, Jane, and Bob with corresponding quantitative metrics.
row 2 has names Rob, Alison, and Ted with corresponding quantitative metrics.
I would like to create a visual in the dashboard where it aggregates data per each name for one row. So for example, I want to calc the average score for Rob (i would like to consider rows 1 and 2 for the aggregation). how would i do that?
I understand the requirement but the best way to implement this would be data preparation at back end. Or edits in custom sql if the data is ingested through custom sql.
Because listing all users will be a challenge in itself as this would need column to row transformation.
hi @prantika_sinha that is what i figured, thank you. an additional question i had on that though because i tried to access the sql query editor, does this only work on data sources uploaded to a database? the current data i am working with is an excel file manual upload. the plan for the future is to leverage a redshift but still working through kinks so having to use excel uploads for now to figure out what kind of views we want.
If you are using an excel file, you can transform the data using excel. For Row to column transformation, you can take help of Pivot Table and ingest the transformed data.
yes, but still wanted to check if it is possible to use sql in quicksight with a manual upload or if it is not intended for that. i ended up using power query for now.