how to duplicate fields

[WuCong]

I have one table ,which have many fields. primary key is field1.

field2 - filed N is other fields.

now I want to check the field2-field N has no duplicate info.

how can I check.

such as 1001 A A C ,this situation has duplicate info.

[AMiller_Tri]

Hi @wu cong?, thanks for the question!

A possible solution would be to create an array out of all the columns - and then use the ARRAYLEN() and ARRAYUNIQUE() functions to see how many unique values are in the array. If the value is smaller than the number of columns, this row has duplicates.

We use the 'nest columns into objects' transform to create the array. In order not to pick all the columns manually, you can use the columns view and shift-select the first and last column. That way, all the columns will be included in the transform:

Now we create the 'column_count' column by using the ARRAYLEN() function on the created array:

Now we create the 'column_unique' column by nesting the ARRAYUNIQUE() function within the ARRAYLEN() function:

In my case, in all records, there's a duplication. But the last step remains the same; Creating a "is_duplicate_info" column, depending on whether the 'column_unique' and 'column_count' columns are equal:

There is an alternative approach, where you unpivot all the columns and count the distinct values of each grouped record - but then you'll need to pivot back again, and it's a less performant solution.

I hope this helps; please feel free to ask any further questions.

Thanks,

Amit.

[WuCong]

hi Amit

thanks a lot.

there is one more question. in the column ,there are null value. so have some problems.

[AMiller_Tri · Accepted solution]

Hi @wu cong? 

In that case, you can do an extra step before using the ARRAYLEN() and ARRAYUNIQUE() functions.

Given the data you're showing me, some values contain '[' and ']', which might conflict with extracting values from an already-existing array.

Thus, I'd suggest a different approach.

First, instead of creating an array, merge the columns, with a comma as a separator:

Here I've selected the relevant columns and chose the suggestion to "merge column, separated by ',' ".

But you can also do it manually with the "merge" transform.

Once doing so, you can use the "Extract matches to array" transform, to make sure you only extract non-empty values of this array, to a new array; like so:

in here, I've used a regular expression: /[^,]+/

Inside the brackets, I write the options for the possible characters. By writing "^," I'm saying "anything that is not a comma".

The "+" means "an unlimited amount of occurrences".

In total, I'm extracting "an unlimited amount of occurrences of characters what aren't a comma".

This is why the full values are extracted.

I would've suggested an easier approach, but the variety of possible characters in your column requires s delicate touch =]

I hope this helps.

Feel free to ask any further questions.

Thanks,

Amit.

[WuCong]

you are wonderful. thanks a lot ,amit.

[AMiller_Tri]

You are very welcome, @wu cong?! Thanks for being an active part of our community and helping it thrive!