Rearranging joined data

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?

REFERENCE INFO.PNG

Help

Join

Accepted answers

tim-regas

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

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

1281057.yxmd

All comments

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

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

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

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

1281057.yxmd

Raj

@jdobbins
please add some sample data.

jdobbins

Thank you this worked for me!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280