substituting repetitive values

Hello I have an excel file with thousands of records. I have the invoice numbers of the left in the table below. I need to compare the records and wherever the invoice numbers are repeating, I need to substitute with a symbol. For example 1050058722/23/24/27/33/35/36 are all exclusive numbers hence the 1 against them. 1050058738 is repeating thrice hence 1 on the first row and - and - on the same numbers that follow. How do i get this done in Alteryx. I am just a Beginner

1050058722 - 1

1050058723 - 1

1050058724 - 1

1050058727 - 1

1050058735 - 1

1050058736 - 1

1050058738 - 1 Abhii

Data Investigation

Accepted answers

Qiu

@Inactive User
Maybe something like this?

0703-AbhijeetChib.yxmd

atcodedog05

Hi @Inactive User

Here is how you can do it. My approach is similar to @AkimasaKajitani's approach. What I could understand is 1st occurrence and last occurrence should be displayed and rest changed to "-"

Workflow:

1. Using sort tool to sort record ID by descending. I am doing this because we need the total in the first occurrence row.

2. Using running total group by on invoice. This way we get the sum.

3. Using sort tool sort record ID by ascending. Reverting it back.

4. Using multi-row formula tool I am checking if the above invoice same as the current invoice if not it's the first row. If below invoice is not same as current invoice its last row. And flag them as 1 rest as "-"

5. Using formula to keep first and last occurrence and replace all with "-"

Hope this helps 🙂

first last and total.yxmd

All comments

surajmthomas

You would need to use the summarize tool get the count and then proceed. It would be helpful if you attach a sample data set in .xlsx or .csv format and also give a snap shot of how you would want your output to look like

surajmthomas

Not sure if this is what you're looking for

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Return-quot-0-quot-on-multiple-match-based-on-criteria-in/td-p/780740

AkimasaKajitani

Hi @Inactive User

If the input is as follows, you make the workflow of sorting and using Multi-Row formula and concatenating the fields to achieve your requirement.

The important point is using Multi-Row Formula. In Multi-Row formula, you can compare between the previous row and current row and if they are the same, you can count up the value of count up field.

IF [Row-1:Field1]=[Field1] THEN [Row-1:RepititionNo]+1 ELSE 1 ENDIF

I attached the sample workflow. Please check it.

MultirowFormula_AK.yxmd

Qiu

@Inactive User
Maybe something like this?

0703-AbhijeetChib.yxmd

unknown

Thank you everybody. I appreciate the responses and i found more than 1 way to do this.

@Qiu @AkimasaKajitani both your solutions worked perfectly. Thanks a lot.

unknown

Thank you again. Adding on the below data, I need to add the amounts in the repeated invoices on the first row. Here is sample expected and i have added the excel file this time too.

SampleFile.xlsx

atcodedog05

Hi @Inactive User

Here is how you can do it. My approach is similar to @AkimasaKajitani's approach. What I could understand is 1st occurrence and last occurrence should be displayed and rest changed to "-"

Workflow:

1. Using sort tool to sort record ID by descending. I am doing this because we need the total in the first occurrence row.

2. Using running total group by on invoice. This way we get the sum.

3. Using sort tool sort record ID by ascending. Reverting it back.

5. Using formula to keep first and last occurrence and replace all with "-"

Hope this helps 🙂

first last and total.yxmd

unknown

That worked like a dream, thank you @atcodedog05

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703