Display values of 2 fields one below other on the table

Question & Answer
1

Hi All,

we are currently migrating Tableau reports to QuickSight. In a table object, we have to display the value of 2 fields 1 below the other as shown in Column2 in the screenshot. since the data type of both fields are different, I am getting error when concatenating the values and not able display them in different lines within same row. Please help here.

In Qlik, we can use Chr(10) for this purpose. is there a relevant function or option here

@Max @Sanjeeb2022 @Biswajit_1993

image

1 Like
2

Hi @MadhuB - The best way is to cast to string first and then concatenating it.

Regards - Sanjeeb

3

Hi @MadhuB ,

Yes we can do this by using concat() and tostring().

PFB the sample example.

I have two field one is Tenure(Number Type) and Employee Name( String Type)
So as per my understanding you want in the First Line Tenue will come and in the next line the Employee Name will come like this.

image

So for achieving this I am using the below calculated field.

image

In your case just add column2 in place of Tenure and column 1 in place of Employee Name and use the same syntax for achieving the desired result.

For more details please follow the link.

Thanks & Regards
Biswajit Dash

3 Likes
4

@Biswajit_1993 Thanks for your response.
the first field is an integer. so, when i cast that to string, the thousand separator is not available. Is there any solution for it

2 Likes
5

Hi @MadhuB,

You could use an ifelse statement that checks the length of the integer and adds separators in manually, not very elegant, but this quick example would work for integers up to 6 characters in length as an example:

ifelse(
    strlen(toString(MyInt))<3,
    toString(MyInt),
    strlen(toString(MyInt))>3 AND strlen(toString(MyInt))<7,
    concat(substring(toString(MyInt),1,strlen(toString(MyInt))-3),',',substring(toString(MyInt),strlen(toString(MyInt))-2,6)),
    toString(MyInt)
)

If you can apply this kind of string manipulation before the QuickSight analysis phase (either in a SPICE dataset calculated field or the backend data source) this would also improve your dashboard performance.

Regards,

2 Likes
6

Hi @MadhuB - Iā€™m marking @Biswajit_1993 's response as the solution to your question. @abacon 's solution to your follow up question is valid as well. Marking response to original question as solution.

Regards,
Arun Santhosh

2 Likes