Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
A_PR
Contributor III
Contributor III

Unique values in straight table based on another column

preeti1_0-1720625635105.png

Hi All,

I want to show unique values in the column - comment and it should be grouped based on survey name column.

I have attached the raw data and desired output. Is there any way where this can be done. Please help.

Thank you

2 Solutions

Accepted Solutions
Sayed_Mannan
Creator
Creator

To show unique values in the Comment column grouped by Survey Name , you can use the Aggr function with Concat to create a new field in your load script. Here’s how you can do it:

LOAD
[Survey Name],
Aggr(Concat(DISTINCT Comment, ', '), [Survey Name]) as GroupedComments
RESIDENT yourTable;

After reloading your data, you can use the new field GroupedComments in your straight table. This will show unique Comment values grouped by Survey Name. 

View solution in original post

A_PR
Contributor III
Contributor III
Author

@Sayed_Mannan - Thank you so much for your response.

I did something similar and created a new column with the formula: =Concat(aggr(if(Count(comment)>=1,comment),survey_name,comment),',')

View solution in original post

4 Replies
Sayed_Mannan
Creator
Creator

To show unique values in the Comment column grouped by Survey Name , you can use the Aggr function with Concat to create a new field in your load script. Here’s how you can do it:

LOAD
[Survey Name],
Aggr(Concat(DISTINCT Comment, ', '), [Survey Name]) as GroupedComments
RESIDENT yourTable;

After reloading your data, you can use the new field GroupedComments in your straight table. This will show unique Comment values grouped by Survey Name. 

MatheusC
Specialist
Specialist

@A_PR 

To consolidate these values ​​into one, there is an interesting tactic.

With your comment column sorted, in the color expression of the graphic text put:

Use the Up function to see the previous value and then hide the text with white color

if(only(comment) = Above(total comment), white())

*It can be useful, but it can also get confusing if you change the column sorting.*

- Matheus

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
A_PR
Contributor III
Contributor III
Author

@Sayed_Mannan - Thank you so much for your response.

I did something similar and created a new column with the formula: =Concat(aggr(if(Count(comment)>=1,comment),survey_name,comment),',')

A_PR
Contributor III
Contributor III
Author

Thank you so much for your response @MatheusC However, there is a huge possibility that the user may sort the data. I have shared my solution.