Hi Team,
I have linked a dataset in Topic. One of the column and key identifier is Invoice_number. We basically use this Invoice_number as reference to get the payment status and other details from the dataset. In few scenarios, it is unable to provide correct status. For example: for invoice 2059262257 it is give ABC as status, but the correct status is XYZ in the dataset and the dashboard. When asked about this mismatch, it is providing that the system is treating this as 0009262257 may be as nearest something. I want exact match. I have already checked the data type and it is TEXT. Attaching the conversation history below:
Prompt: what could be reason and how can I fix it. as I am able to loacte exact 2059262257 on dashboard. And why are you returing status for 009262257 instead of 2059262257
Assistant Reply:
You’ve identified an important issue! The system is converting your invoice number 2059262257 to 0009262257, which is causing the mismatch between what you see on the dashboard and what I’m retrieving.
Why this is happening:
The QuickSight QueryTopic tool is treating the Invoice_Number field as a numeric data type instead of text. When it processes 2059262257 as a number, it’s likely:
-
Truncating leading digits or applying some numeric transformation
-
Converting it to 0009262257 (possibly taking the last 7-10 digits)
-
This explains why you can find the exact invoice 2059262257 on the dashboard (where it’s stored as text), but my queries are returning results for a different invoice number
How to fix this:
Based on your long-term preferences, you’ve already noted that you want the Invoice_Number column to be read as Text instead of Numeric. Here are the steps to fix this:
-
In QuickSight Dataset Settings:
-
Navigate to your SOA datasets (SOA Shortpaid Reason and SOA CS)
-
Find the Invoice_Number field
-
Change the data type from “Integer” or “Decimal” to “String” or “Text”
-
Save and republish the dataset
-
-
In the Topic Configuration:
-
Go to the Invoice_Arc_Topic settings
-
Verify that Invoice_Number is configured as a text/string field
-
Refresh the topic to pick up the data type change
-
-
Alternative workaround (if you can’t modify the dataset):
-
When searching on the dashboard directly, you can apply filters with the exact invoice number as it appears in the source data
-
The dashboard interface may handle the data type differently than the QueryTopic API
-
Why you can locate it on the dashboard: The dashboard’s visual interface likely preserves the original text format of the invoice number, while the QueryTopic API is using the underlying dataset’s numeric data type definition.
Would you like me to help you with specific steps to modify the dataset configuration, or would you prefer to work with your QuickSight administrator to make this change? Once the data type is corrected, my queries should return accurate results for invoice 2059262257.
Prompt: I checked - the data type is text for invoice_number for both - QuickSight Dataset & Topic Configuration.
Assistant Reply:
Thank you for confirming that the data type is set to text in both the QuickSight Dataset and Topic Configuration. This is helpful information.
The issue is that the QuickSight QueryTopic API is still internally converting your invoice number 2059262257 to 0009262257 despite the text data type setting. This appears to be a limitation or bug in how the QueryTopic API processes certain text fields that contain numeric-looking values.
Why this is happening:
Even though the dataset and topic are configured with text data type, the QueryTopic API may be:
-
Applying automatic type inference during query processing
-
Truncating or transforming the invoice number before matching
-
Using a different internal representation than what’s displayed in the dashboard UI