When i am uploading incremental data into SPICE getting duplicate records , is there any configuration in Quicksight which help to avoid duplicate into Quicksight .
Hello @Raulsc,
Do you have rows that were updated outside of the the look-back window? If you need records updated, make sure they are within look-back window.
If you need to avoid duplicates, you can create a calculated field (in dataset or analyses) consisting of running count partitioned by key, order by the date you’re using in your incremental refresh. Filter the columns that are equal to 1. Ideally, you’d want to create this logic prior to loading data into SPICE.
2 Likes
How I could create this calculate field? I try created but I can’t. In this question I explain my problem Delete duplicate with Incrementally refreshing a dataset
If you could said me the solutions , will be amazing!
Thanks
Hi @Raulsc -
The best way is to do it is in the database using SQL and CTE (create a CTE with only 2 columns, PK and max Date by PK, and then do an inner join this table with the original table) to retain only the latest/max date.
If you cannot do it there then I would do the following:
- In QS, when editing the dataset in the data prep layer, create a calculated field that identifies the latest date by PK:
- Publish and Visualise
- In the analysis, create a new calculated field ‘IsLatest’
- use this field (=1) to filter all visuals
Hope it helps.
If this idea works, please mark it as a solution. Thanks for posting your questions on the QuickSight Community Q&A Forum!
1 Like