Hi everyone, I'm working on an Alteryx workflow where I'm processing data through multiple containers to create new columns based on specific conditions. I need to perform a full join to compile all this information into a single table without losing any values. I have tried couple of things but i am not getting the output that i desire... Here is an exmaple of the desired output Please help :) roproductsubmission_target_datesubmission_lbe_datesubmission_actual_dateapproval_target_dateapproval_lbe_dateapproval_actulal_dateoverduemissingRO -6SolarFlare2/9/202412/18/2024 10/13/202311/22/20237/23/2024TRUEFALSERO -7NanoShield6/11/20244/9/2024 12/30/2024 TRUETRUERO -8TurboBlaze11/22/20248/4/2024 11/15/2023 TRUETRUE Full_Join.yxmd

Help Needed with Full Join

Hi everyone,

I'm working on an Alteryx workflow where I'm processing data through multiple containers to create new columns based on specific conditions. I need to perform a full join to compile all this information into a single table without losing any values. I have tried couple of things but i am not getting the output that i desire... Here is an exmaple of the desired output

Please help

ro	product	submission_target_date	submission_lbe_date	submission_actual_date	approval_target_date	approval_lbe_date	approval_actulal_date	overdue	missing
RO -6	SolarFlare	2/9/2024	12/18/2024		10/13/2023	11/22/2023	7/23/2024	TRUE	FALSE
RO -7	NanoShield	6/11/2024	4/9/2024			12/30/2024		TRUE	TRUE
RO -8	TurboBlaze	11/22/2024	8/4/2024			11/15/2023		TRUE	TRUE

Full_Join.yxmd

Workflow

Accepted answers

Qiu

@Vineet003
I would like to comment as below

1. we can use the Multi-Column tool for the conversion of DateTime, rather than 6 DataTime tools.

2. We can combine the Filter and Formula tools with the conditional statement in one Formula tool.

Hope I understand you correctly.

Full_Join_r1.yxmd

All comments

Qiu

@Vineet003
I would like to comment as below

1. we can use the Multi-Column tool for the conversion of DateTime, rather than 6 DataTime tools.

2. We can combine the Filter and Formula tools with the conditional statement in one Formula tool.

Hope I understand you correctly.

Full_Join_r1.yxmd

Yoshiro_Fujimori

Hi @Vineet003 ,

If you want to check if each transaction is "overdue submission" or "overdue approval" or "mising information", you can add boolean fields without joining.

(FYI: For conversion of date to Date type on multiple columns, you can do it at once with Multi-Field Formulat tool.)

I hope this helps.

Workflow

Formulat Tool: for Status Checks

Overdue Submissions = [submission_lbe_date] < [date_report_created] AND IsNull([submission_actual_date])

Overdue Approvals = [approval_lbe_date] < [date_report_created AND IsNull([approval_actulal_date])

Overdue = [Overdue Submissions] OR [Overdue Approvals]

Missing Information = (

(NOT IsNull([submission_actual_date]) AND IsNull([submission_lbe_date]) AND IsNull([submission_target_date]))
OR (NOT IsNull([approval_actulal_date]) AND IsNull([approval_lbe_date]) AND IsNull([approval_target_date])))
OR (IsNull([approval_target_date]) OR IsNull([submission_target_date]))

1274489.yxmd

Vineet003

Thank you both @Qiu and @Yoshiro_Fujimori . I can't believe how simple this is. I am laughing at myself looking at my original workflow 🤣

Qiu

@Vineet003
Good to know it works.
No need to laugh at yourself.
There is always time that we just need a bit hint to get out the stuck, no matter how good your skills are.

Have a good day.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703