How to count the occurrences of a specific value in a column while in Data canvas

[Natygan]

Hello all,

I'm preparing my data in the Data canvas section. In my table, I've got a column with the document number.
My need is : if a specific document number appears on several rows, then I want to delete every row that has this specific document number.

I assume that the easiest way to do this would be to add a column with the number of occurrences of a given document number in the table, and then add a "Filter rows" step to keep only the rows with the number of occurrences equal to 1.
But I can't find how to do this. Is this even possible ?

Thank you for any idea !
Nathalie.

[Olivier Keugue Tadaa]

Hi Nathalie,

if we consider this table structure 

you can create a calculated column "occurrences"

occurrences :::: Count([value]) over ([doc num]) 

and then filter to only show document numbers appearing only once

 

 

I hope this helps

[Olivier Keugue Tadaa]

alternatively, you can use the "pivot" transformation from the data canvas (you should ensure you've selected the proper rows identifiers and applied the appropriate aggregation methods)

 

 

[Natygan]

Thank you so much, Olivier, for this very quick and perfect answer !! :-)

Have a nice day,
Nathalie.

[Olivier Keugue Tadaa]

You are welcome, Nathalie