Table Transformer Macro: how to remove duplicates attributes in different rows

[silvio croce]

Hello,

I need to remove duplicates from a table that I created getting issues from jira using the table transformer macro.

for example if I have a table like this:

colun1 | column2 | column3

1          | 2             |3

1          | 2             |4 

5          |  6            | 7

Should be transformed into:

colun1 | column2 | column3

1          | 2             |3

            |                |4 

5          |  6            | 7

Of course I need all of this to be dynamic and not custom to a particular table.

Thanks in advance

[Katerina Rudkovskaya _Stiltsoft_ · Accepted solution]

Hi @silvio croce,

The simplest way to aggregate tables is to use the Pivot Table macro:

And here is another variant using the Table Transformer macro:

SELECT 'column1', 'column2',
CONCAT_VIEW_AGGR(FORMATWIKI('column3' , "\n")) AS 'column3'
FROM T*
GROUP BY 'column1', 'column2'

Hope it helps your case

[silvio croce]

Hello  Katherina,

thanks for the 2 suggestions, they are both useful! They can make it for my case and I accept your answer. Your answer was very quick thanks again!

just for the sake of my curiosity, is there a way to loop the table taws and empty the cells according to some condition on the previous row?

thanks in advance

[Katerina Rudkovskaya _Stiltsoft_]

Not sure that it is exactly your case, but you'll be able to delete (or change) data from table cells based on this cell's value or based on the value from another cell (but in the same row). You may browse our documentation for the "CASE... WHEN..." examples.

The Table Transformer macro itself is based on the AlaSQL library and treats its source table as a mini SQL database. So we are a little bit limited here.

[silvio croce]

thanks again, I know the CASE WHEN possibility. It is very usefull.

I think you touched the core of my question when you say "but in the same row", I would need "also in the previous row" :-) 

I would need for each column of the table, compare the value of the current row with the one of the previous row, and in case they are identical put a blank value.

This is what I would dream of :-)

but thanks again, already the pivot table does a lot of what I need. 

[Stiltsoft support]

Hi @silvio croce ,

It seems that aggregating via the Pivot Table macro and grouping via the Table Transformer macro (the GROUP BY option) is the closest touch we have.

The Table Transformer macro also supports the DISTINCT function - maybe it will come in handy for other cases with duplicate entries.

[silvio croce]

thanks a lot! I'll give it a try.