Match tables data

[Juacata]

I can't find the way to join two tables by a column and leave in null or blank the rows that doesn't exist in the first one.

Below you can see an example of how the information can come:

This is what i need to output:

Age group	Count
Total # of Receivable	2.495
0-30 Days	807
30-60 Days	788
+60 Days	832
Total $ of Receivable	36361895.9
0-30 Days	14086065.61
30-60 Days	14349579.53
+60 Days	7926250.76
Total # of Payable	3.102
0-30 Days
30-60 Days	915
+60 Days	2.187
Total $ of Payable	27787401.5
0-30 Days
30-60 Days	1252104.02
+60 Days	15635297.48

[stvnhdmpa]

@Juacata  What are you trying to join on?  I see the same data age groups duplicated, so it seems like you probably have multiple criteria.  Can you union the items that don't have a match?  If you can attach a sample, I'm happy to take a look.

[JBLove]

Hi @Juacata ,

Based on the details provided, it sounds like what you may want to add a union tool after the join tool to stack either the Left (L anchor) and Join (J anchor) outputs or the Join (J anchor) and Right (R anchor) outputs.

If the column names are the same you should be able to use automatically align by Column Name or position, but manually aligning is an option too.

[ddiesel · Accepted solution]

Hi @Juacata!

There has GOT to be an easier way to do this, but here's where my brain went with it:

The tricky part is retaining the original order of fields while inserting missing ageing groups. This should be a dynamic solution even if the ageing groups change. If the ageing groups are always static, then we can take out some of these steps.

If anyone can come up with a simpler solution, I'd love to see it! In the meantime, please let us know if this works for you.

Thanks,

Deb

[binuacs · Accepted solution]

@Juacata similar to @ddiesel approach

[Juacata]

It works perfect!! really appreciate your help! i'll never think of such a solution.

[Juacata]

Nice solution too @binuacs , Thanks for your help!!

[ddiesel]

Glad it helped!

@binuacs, thank you for streamlining the logic! I liked your approach.