DataSource has (Changing) Dates as Field Names

Question & Answer
,
1

Hi there. My data source is an Excel spreadsheet that includes 30 columns that represent the count of actions for a given date:

image

The dates represent the last 30 days of activity. I need to use these metrics in my visualizations but because the field names changes (based on the last 30 days) I am not sure the best way to proceed.

I tried adding additional fields within Excel to calculate what I want (so QuickSight has access to those fields) but that adds another manual step in the process. I had considered a “for each field” approach but that’s not available in calculated fields (since I wouldn’t care what the header is.)

The other problem I have is that these files get generated weekly - so when I tried to replace my datsource the column count will stay the same but the dates/field-names will change so I think I’ll have to recreate the datasource/visualizations each time.

Open to any thoughts you may have.

Cheers,
Jason

2

Hi @goosh
any chance you can transpose the file? That will have the dates in one column and the values in another?
BR

1 Like
3

Well, unfortunately the data is extracted out of another tool - which we have limited options for. We could build a richer ETL to transform and improve (which was done prior in Tableau, which we are looking to migrate from).

Is there an ETL process within Quick Sight or would we have to transpose outside before ingesting?

4

Within QS isn’t a ETL functionality. In AWS for sure.

In my opinion it would be better anyway. In case you would like to have more than 30 days you’ll be flexible.

5

Thanks Erik. That’s where I was leaning too, we can transform and write to Athena via AWS. Thanks & cheers.