Removing blank values for single cell value new line concept

Question & Answer
,
1

Hello Team,

recently i was on working on a report,
In my SQL logic in single cell value there will be multiple values using new line concept.

but i am facing one issue if the value is null then it shows blank value how we can remove those?

for example: these values will come in single cell
ab;

cd;
dc;
dc;

se
now i want to remove the blank spaces in between

expected output
ab;
cd;
dc;
dc;
se

another example:
image
in the above image i want to remove the spaces after the latter

please help me with this thank you!

Thanks & Regards,
Deepak Kumar Pradhan

2

Hi @Deepak1

You can use replace function to remove blank space in string filed.
example : replace(‘1 and 2 and 3’, ’ ', ‘’)

1 Like
3

Hello @Ashok,

thank you for responding,

this actually works when there is a space in single cell
but in the sql we have written query like if there are multiple values then it should come in new line but few values were null that is why it is coming blank space now we are trying to remove that blank space.
in sql it was difficult that is why we are trying to resolve this in reporting level.

Thank & Regards,
Deepak Kumar Pradhan

4

Hi @Deepak1

Unfortunately, there is no reporting level there is no option remove space which are not in consistent in length.
This has to be resolved at SQL level or perform additional ETL to cleanup the spaces and ingest the data. Can you try excluding null values, before doing newline ?

5

Hi @Deepak1,
It’s been awhile since we’ve heard from you. I wanted to check in and see if you were able to try out Ashok’s suggestion and if it worked. If not, are you still running into a similar problem?

If we do not hear back in the next 3 business days, I’ll archive this topic. If you have follow up questions or concerns, you can open a new topic for discussion.

Thank you!

6

Hi @Deepak1, I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)