Hello - does anyone have troubleshooting tips for “skipped xxxx rows where an undefined exception occurred”? Quick Sight is not giving me an error file. It occurs when I left join 2 SPICE datasets using the “add data” option. I own both datasets, and neither produces any refresh errors independently. The primary dataset has 1587 rows and is a SPICE import of a Redshift table. I am doing a left join with a dataset with 196 rows (a custom SQL query set to SPICE) on an integer id field that exists in both. Whenever I do this, I consistently have an issue where I end up with 566 rows in the dataset and 1021 skipped rows. I am having trouble finding anything to guide me on how to approach fixing this.
Hi @nicdonne
Welcome to the Quick Sight Community!
You are creating the third dataset when this issue occurs right?
- Data types for some columns could be one of the issues since you are joining datasets of two kind.
Can you share details of the two datasets (Fields, Data types) as well as your join?
Regards,
Giri
Hi @Giridhar.Prabhu - I guess I am unclear on how the data types could impact this since it works fine in SQL. The primary data set is just a detailed one with a lot of renaming and calculated fields I would prefer not to have to recreate by doing this in SQL. The “left” dataset has 66 fields, without calculated fields created in Quick Sight, that are int, varchar, numeric, and dates. The supplemental data I am joining is 3 fields - an int id field that is the common field in the two sets, a varchar field, and an int that is a rank computed in the in the custom query that creates the dataset. Everything in the second table appears at least once in the primary table. I am creating the join by going into “edit dataset” on the primary dataset and clicking the “add data” button, then creating the left join on the common id field.
Hi @nicdonne - When you are joining 2 data sets , ensure both data sets are having same data type on the joining key. if both data sets are in SPICE and data type for the joining key is also correct, then there something weird in your set up. Is it possible to share some screenshot to show the joining key and data sets details ( Note - If your data contain sensitive info), please do not share the data.
Regards - Sanjeeb
Thank you for the assistance @Giridhar.Prabhu and @Sanjeeb2022. I cannot post any screenshots due to the nature of the data. However, I discovered the issue, but not a solution within Quick Sight. On a whim, I removed the rank calculation from the SQL in the dataset I am joining with and there were no errors.
The rank is a critical piece of info. I would have thought that each dataset is refreshed independently and then joined in Quick Sight using this method, but the errors due to the rank calculation suggest otherwise. I am going to have to revise the ETL that creates the primary table to include the logic in the secondary dataset that I am trying to join instead of doing it via Quick Sight. This isn’t ideal, but I am not sure how else to accomplish this at this time.
Hi @nicdonne
Just to understand the issue better, is the dataset with issue in question a third dataset and you have your Custom SQL dataset already saved as a SPICE dataset?
If the Custom SQL dataset with Rank is already saved as a SPICE dataset then I don’t understand why it would cause a problem.
However, if you have your Custom SQL dataset and in that you are trying add the other dataset or vice-versa then there is a question of a query dynamically running and we may to look at what is happening behind the same.
You don’t need to share any data but can you show your dataset screen where you have the two datasets being joined to get an idea?
It looks like when I did a save & publish it was fine, but it starts having issues on subsequent full refreshes. The primary dataset, goals_mbr_qbr, is a Redshift table used via SPICE (not direct). I am using the “add data” option to join it to the second dataset. The dataset I am joining it with is a dataset I created in Quick Sight using Custom SQL. It is also SPICE. When I join them into a third dataset, the errors occur.
Hi @nicdonne,
It’s been awhile since last communication took place on this thread, were you able to find a work around for your case or are you still facing the same issues?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Thank you!
Hi @Brett - I was not able to get this specific one to work as described. I ended up adding the data in my ETL so it was part of the larger dataset. I did find, when trying this again with different data, that which dataset you started with when selecting “add data” does appear to make a difference, but I have not tried to figure out why. For example, if I am trying to create a join on datasets A and B, if I start with A and use “add data” to add B, I get errors and rows that fail to import. But if I start with B and use “add data” to add A it works without issues.
1 Like
Hi @nicdonne,
Interesting, thanks for sharing the additional information! If you have any additional questions, feel free to create a new post in the community!