I have a table that contains payment data as well as any adjustments for a lead id - so there can be multiple rows per lead id. I have a table that only shows 2 rows of data when there should be 3. The timestamp for two rows are exactly the same so it is summing those 2 rows together for a total of 2 rows in the analysis instead of 3. I set date aggregation to seconds but the 2 rows are still combined - is there a way to get all 3 rows to show?
Hi @so2605 - Are you creating any custom data set ( joining tow tables or put any filter) in preparing the final data set in QuickSight. It will be good if you can provide some screenshot on your data set preparation page so that we can understand how you are creating at QuickSight side
Note - Please do not share any PII or PCI details.
Regards - Sanjeeb
1 Like
@Sanjeeb2022 it is custom sql - I just added a row_number field to the dataset and hid it in the table analysis so I think I am good. But if there is a way to do it without having to add the row_number field I would like to know it
Hi @so2605 - Please provide the details on the custom sql like what exact sql you are running. Also if possible please run the custom sql in the db to ensure there is no issue in the sql code.
Regards - Sanjeeb
1 Like
postgres - no issues with code
Hi @so2605 - Ok. QuickSight never filter any rows ( unless until you have put some condition explicitly) , it will use database the connection and fetched the data. Can you please create a report and put a filter on specific data to validate it with DB.
Regards - Sanjeeb
Hi @so2605 - one more point, i assume you have not use SPICE, it is a direct query mode.
Regards - Sanjeeb
1 Like
there aren’t any issues with the query - it is just that two timestamps are exactly the same (which is correct) and when I make a table analysis with the lowest date aggregation the two rows are still combined but in the dataset editor I can see the 3 correct rows. I just want them separated out in the table analysis. I am using spice for this dataset
Hi @so2605 - Ok are you saying you have exactly duplicate records, in that case you can add a row_number() to have a unique ID for each row.
Regards - Sanjeeb
not completely but the timestamp for 2 of them is exactly the same so in the analysis they are combined into one row - I added the row_number field (which works) but is there a way to do that in QS without having to add the row_number field?
Hi @so2605 - You can add a calculated field with current timestamp ( with milli second) so that each row have a unique timestamp or row_number to have a unique ID for each row. I do not think there is any other approach available.
Hi @Koushik_Muthanna @SD_QS @Max - Can you provide your expert advise on this. In source data there are duplicates, how we can show all in QuickSight. Apart from row_number or current timestamp is there way we can bring all data to QuickSight. Please advise.
Regards - Sanjeeb
1 Like
@so2605 Can you check if your date field is set as a “measure” or “dimension”? If it is set to measure, try changing that to dimension because as you know measures are aggregated by default.
1 Like
Hi @so2605
We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.
1 Like