Duplicates with missing data

[maxwell123]

Hi Alteryx Community,

I’m facing an issue with duplicate entries while exporting data from our ERP system. The data is being pulled into Alteryx, but I’m encountering duplicates where:

• One duplicate has all the data, including the MS code, but the ECN code is missing.
• The other duplicate has all the data, including the ECN code, but the MS code is missing.

Here’s an example of the data:

ECN in Yellow, MS Code in blue.

 

Currently I am using a Multi-Row Formula tool with this Expression for both columns:

IF ISNULL([ECN]) THEN
[Row-1: ECN]
ELSE
[ECN]
ENDIF

However, I am running into two issues with his:

1. I am still getting blank 1 blank value at the top of the table, I am assuming because the equation is not accounting for the first row.

2. I am trying to use these two multi-row formula tools (One for MS the other for ECN), however When they are used in conjunction with my batch macro, that of which is pivotal to the workflow, the tools don't even populate anything. They don't throw me an error, however it's like they are ignoring the Expression I wrote above, and the columns are still null in the output. For reference when tested on a workflow without a macro, they worked fine.

How can I properly full in these null columns with the correct data then eliminate the duplicates?

[Carolyn]

Could you use a Summarize Tool to Group by the first 2 fields and then Concatenate the ECN & MS Code fields? If it's just a case of one blank and one non-blank, this should work fine. If you have multiple non-blanks, then you'll get them concatenated (e.g. "EAR99, EAR99") which is probably not what you want :) 

[SPetrie · Accepted solution]

Another possible method is a transpose on the id, filter out the null or empty values, and then cross tab it back taking the first of each item. That assumes true duplicates of the individual Id values.

[nagakavyasri · Accepted solution]

Hi @maxwell123 , try this