Rearranging joined data

[jdobbins]

I used the join tool but maybe there is a better way to add in all my records to not have null spaces? I am wanting to join all 5 columns and have the output info be on the first and second row under their respective header. Or just a way to add them all in a neater way. Join adds them in with a bunch of Null.. Is there a better tool for this or a better way to use join? 

[shancmiralles]

hi! you can try and filter out all of the nulls  so use 5 filter tools for each columns and use [column] != "[Null]"

then attach a select tool for each to remove the other columns (so for example for teh first filter you filter out the nulls from the 1st column.. so in the select tool remove columns 2-5.. and so on..

then use the "join multiple" tools to combine them all back.

[tim-regas · Accepted solution]

Hi @jdobbins if you are able to provide a source file with some dummy data that would be a big help!

The reason you are ending up with so many nulls is because you are using a union tool which will generate null values for columns that don't exist in the other inputs. If there are only two terms you could setup two paths and use an append tool for each term. If the terms are going to be dynamic, you would need to keep the terms to join back to.

Using Group By in the summarize tool helps to create two (or more) groups and sum the invoice amounts for that specific term.

[Yoshiro_Fujimori · Accepted solution]

Hi @jdobbins ,

If the #2 ~ #4 data flows contain one row for each, you may want to use Append Fields tool instead of Join tool.

I hope this helps.

Workflow

Output

Contract Terms	Sum_Outstanding	Date Opened	Last Sale	Avg_days to pay
Net Due in 45 Days	26733674.29	2023-11-30	2024-05-29	69.666667
Net Due in 30 Days	26733674.29	2023-11-30	2024-05-29	69.666667

[Raj]

@jdobbins 
please add some sample data.

[jdobbins]

Thank you this worked for me!