How to show Duplicate values in Table visual

Question & Answer
, ,
1

Hi all,

I would like to show the Duplicate rows in the table visual. All my columns are on Group container of the table visual and I am not having any columns in the measure container

For instance, if my o/p look like the below table in athena and i want the same in the table visual too without having to aggregate any columns

Country | Region | credit | debit

US | Illinois | -25.40 | 50.56
US | Illinois | -25.40 | 50.56
France | Paris | -20.50 | 80.89
India | New delhi | -56.20 | 90.90

i get the below result in the table visual now, but i am expecting the result something like the above table

Country | Region | credit | debit

US | Illinois | -25.40 | 50.56
France | Paris | -20.50 | 80.89
India | New delhi | -56.20 | 90.90

Thanks in advance

2

Hi @lalprasanth - Looks like it is extracting the distinct result, can you create a custom sql and put “select * from <>” and see the outcome.

Test with sample data so that it will help you in implement the main case.

Please posted the outcome of the POC.

Regards -San

3

@Sanjeeb2022 i can see the Duplicates when i write SQL queries but only when i pull those columns into the Group container of the table visual, i cannot see them

If i sum credit and debit in the measure container, it actually considering the Duplicate values too but in my case i don’t want anything to be measured. Exact same result of the athena should appear in the table including duplicates when i darg the columns in the group container

4

Hi @lalprasanth , Please use custom-sql like below to include running sequence number .

SELECT row_number () over() as SLNO , a.* FROM “default”.“YOUR TABLE” a ;

And in the table visual card include the “SLNO” column as well in the “Group By” field wells .
And if required you can hide the “SLNO” column after adding it in Group by .

image

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”.
This will help the community to find guidance and answers to similar question.

Thanks
VInod

3 Likes
5

@apjvinod it seems we don’t have any other solution other than having an extra column, because I have rows in millions, and I felt having an extra column might affect the performance. thanks anyway

1 Like
6

@apjvinod @Sanjeeb2022 we can also fix this by keeping all the fields in the value box, that way all the Duplicate rows can be seen in the table visual but the action filter can not be enabled.

I am marking this as solution because it might help people to avoid making row_number() in sql

2 Likes
7

Thank you @lalprasanth for giving us this update and marking as Solution! :slight_smile: