Trim a data

Q&A
1

image
image525×119 2.32 KB

Hi, I want to remove 5 Digit reference from the Invoice and we only need Number-SW

can you please help how I can sort this?

2

Hello there,

First of all welcome to the Quick Sight community.

you may leverage substring function in Quick Sight.

Reference - Substring - Amazon QuickSight

Hope this helps.

Cheers,
Deep

3

Hi Deep,

Please help me to rite this as i am new in to Quicksight and I am learning now.?

4

Hello ,

Example - # If the format is always consistent like (PROJEC-XXXXX-SW):

Try -

substring(
{your_invoice_column},
8,
length({your_invoice_column}) - 7
)


Steps to implement:

1. Go to the dataset
2. Create a new calculated field
3. Use one of the above formulas
4. Replace {your_invoice_column} with your actual column name
5. Apply the calculated field to your visualization

This will transform:

* Input: "PROJEC-330097-SW"
* Output: "330097-SW" 

Hope this gives some idea

Cheers,
Deep
5

Hi Deep,

I have tried this and still getting error? can you please help me?

image
image225×173 1.74 KB

also in one column we have multiple invoices some time like BEAUMO-332269-SW; BEAUMO-332270-SW

is it going to work even though we have more then once invoices?

Thanks
Praveen

6

Hi @8073531642,
Take a look at the documentation page for the substring function, it provides you with a more clear example on how to properly write the expression. The word ‘length’ should not be included in your syntax:

Try switching up your syntax to match the example and let us know if this solves the error issue.

Let us know if you have any additional questions as well, if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

7

Hi @8073531642 - The sample data is not consistent, so substring may not work. Can you please confirm the data pattern, then you can define the rule and extract the necessary details.

Regards - Sanjeeb

8

Hi @8073531642,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you