Joining unmatched columns using a conditional join.

[stj1120]

Hi all,

I have two tables containing 4000 records each. I wanted to do a left outer join for these two tables. But, there is no common column present between these two tables but, to join both we have a conditional join present between these two tables.

How do we actually implement or replicate this in alteryx?

One table has DateTime data type and the other has no date columns. I tried generating the rows for  a new Date column to the second table. Then joined the columns and then used the conditional join in the Filter tool. But, it is giving me 0 records as it is not matching to the Date and DateTime data types. I even changed the new column data type to the DateTime. Even though it is of no use. It is giving me 0 records.

It seems to be little tough for me. Kindly please help me out with the appropriate solutions for this scenario. 

Thanks in advance.

Sreenivasa Teja.

[Dazzerman]

Hi @stj1120 ,

I'm not sure why you would want to join two unrelated datasets, but based on what you have said, you could add a RecordID tool to both data channels and join on RecordID.

I hope this helps.

[stj1120]

Hi @Dazzerman 

Actually I have a Fact table "Calendar" with the columns Caldate (Datetime data type), WorkingDay (Int), CalYear (Int), CalMonth (Int). 

The other table is the view called "Users" with different columns but it has DateStartedCRM (date data type) and DateFinishedCRM (date type). 

Here, I'm trying to get the dates that are in between DateStartedCRM (date data type) and DateFinishedCRM (date type) basing on the CalDate which is in the Fact Table "Calendar".

Join condition:

 dbo.Fact_Calendar LEFT OUTER JOIN dbo.vw_USERS  ON FC.CalDate BETWEEN U.DateStartedCRM AND ISNULL(U.DateFinishedCRM, GETDATE())

How do I actuualy implement this in alteryx to get the same kind of result? Kindly let me know.

Thanks in advance.

Kind Regards,

Sreenivasa Teja.

[Dazzerman · Accepted solution]

Hi @stj1120 ,

If I understand your requirement properly, the attached workflow is one way that you could achieve what you want.

I hope this helps.