Issue joining data to union

[wonka1234]

Hi all,

I am trying to join back my original dataset to the filters and unions I do. (first red box on left)

However I want to keep my same number of records of my unions

The reason I select column by column, filter out the not nulls then union, is because I append all the non null values in each column to one new formula field.(ID final)

The issue is i need the corresponding columns from my first select.. so the columns that belong to those non null for each column.

it is abit complicated but any help would be appreciated.

[clmc9601]

Hi @wonka1234,

Is your goal to get a single list of all the rows that are not null for every column in consideration? Is your issue that the results of your union are duplicating some rows and including some that are null for certain columns? If so, I'd consider using a single filter tool instead of unioning three data streams. Happy to help write the expression if you list the column names.

Alternately, you could use joins and/or join multiple tools instead of the union, as long as you have a row identifier column.

[wonka1234]

@clmc9601  attaching sample workflow.

Is your goal to get a single list of all the rows that are not null for every column in consideration? Yes.

The problem is bringing back the columns that are not in consideration.

[clmc9601]

Hi @wonka1234 Thanks for the sample workflow! Very helpful. Could you please attach the inputsample.csv referenced in the workflow? The easiest way to include is to open the workflow, go to Options > Export Workflow. Then make sure that file is checked. Otherwise, you can just attach it here. Thanks!

[clmc9601]

Only using the metadata from the file template, here are a few recommendations:

- unless you have to specifically remove those columns, just leave them in! I removed all the Select tools.

- flowing Unions into each other has the same effect as using a single Union

- Unless you need to duplicate rows by using all the Filters, I'd just use a single Filter with either AND or OR logic, depending on your needs.

I hope this helps!

[wonka1234]

@clmc9601 

please see attached inputsample.

also as a count I am expecting 202 records in the Mainframe column.

"flowing Unions into each other has the same effect as using a single Union" are you sure? When I do it each time I get 202 records in mainframe.

[clmc9601]

Hi@wonka1234,

Yes, I'm sure about flowing unions. :) Taking out the Select tools changed the counts in my above workflow, not the union consolidation.

Is your goal here to make sure at least one column is populated in each row? If so, I'd go with a single Filter using OR logic like in the attached workflow. Your workflow turns 3522 rows into 6050 rows... duplication like that is usually problematic when working with employee data.

If I still misunderstood your purpose, please let me know!

[wonka1234]

@clmc9601  i like your option 1 - but why is your formula for IDfinal appending all strings.. I should have the count 6505 of one string each for idfinal.

my logic is to take each of the 12 columns, take non null values, make each of those a IDfinal row. so 6505 rows. 1 string in Idfinal each. along with the additional columns..

thank you for your help!!

[clmc9601]

@wonka1234hmm I guess I don't understand your purpose then. I thought you were trying to create a unique identifier for each row based on the values in the columns. I just copied and pasted the Formula you had already.

[clmc9601]

If you're trying to concatenate all the non-null values for each row, it would be easier to Record ID > Transpose > Filter (!IsEmpty([Value])> Summarize (Group by RecordID, Concatenate [Value])