Cleansing Duplicate Data - DIFFICULT

[AlteryxLearner23]

Hi,

I'm finding this very challenging and not even sure if possible.

Using the attached data, under each header of the data, i.e. "Jan-2023", "Feb - 2023" and so on, there is many rows of data.

A lot of these rows are duplicates.

Is there any way I can just have one set of each number?

So, for example, for "Jan-2023", there is 2421 rows of data, summing to -447,448 in total.

However, if you filter on that column, there's actually only 15 numbers which are being repeated.

Those numbers are -60,912, -11,566, -6,750, -1,229, -136, -87, -26, 26, 87, 136, 1,229, 1,978, 11,591, 16,577 and 18,503.

I only want one set of each of these numbers and the same rule to apply to all other months.

At the moment, I have multiple of these numbers. 

Hope this makes sense!

[binuacs]

@AlteryxLearner23 Do you want to filter out all the non-zeros values? 

[AlteryxLearner23]

@binuacs I want to only show the values that are not zero, but only once for each value in each column.

I.E. if there's 100 three times in one row, I only want to show it once.

Thanks

[danilang · Accepted solution]

Hi @AlteryxLearner23 

Looking at your data, you have 16 different key lines, with variations in the following fields 

Type
Forecast Type
Functional Currency
Local Currency
Identify Cross-Currency Leases ("FALSE")
Final Line Item Currency 

Assuming that you want to capture these unique rows, here's a way to find the unique values. 

Transpose all the month columns to give all the key fields plus a name and value field representing the months and the corresponding values.  Group by all columns to get the unique values.  The bottom branch adds a column order to the columns to account for the fact that the Cross Tab tool will rearrange the month columns alphabetically.  After joining to add the column order field, cross tab to get the values back into monthly columns, but using the column order field as the column names.  Finally, rename to get the original Monthly column names 

The result is the sixteen key rows with the unique values from each

Dan

[OllieClarke · Accepted solution]

@AlteryxLearner23 

Here's a couple of approaches depending on what you exactly mean

If you use a summarise tool and group by every column, then that will give you every unique combination of values across all fields (so 

Col 1	Col 2
A	A
A	B
B	A
B	A
A	B

Would become

Col 1	Col 2
A	A
A	B
B	A

The transpose approach will give you every unique value in each column, but treating each column independently

So 

Col 1	Col 2
A	A
A	B
B	A
B	A
A	B

would become

Col 1	Col 2
A	A
B	B

My preferred structure for this type of data is having each date and value in individual rows, rather than columns, so I put that option in there for you too.

Hope that helps,

Ollie