How to handel multiple joins from Calendar table (Day numbers) ?

Hi.

I have just started with Alteryx and need help in creating joins between transaction and time.

My fact table is a combination of head and line data and I have requested and confirmed date as YYYY-MM-DD and thats ok. I need

to join in my calender to fetch the Day number to be able to count days etc.

I wonder how I can do this ? The problem is that som of the dates can be 0 or null and I need to have all rows. When I use the join

I will lose date - is there some way use the left join and still have all colums ?

In Qlikview I used the Applymap function - is it possible in Alteryx ?

Need to join in Requested and Confirmed day number.yxmd

Join

Accepted answers

DataBlender

Yes, you'll need to do two separate joins if the conditions of your join are different.

If you want to do a join where both conditions are required (e.g. where confirmed date = confirmed date AND requested date = requested date) then you can do this within the same join using two criteria in the configuration window. This is obviously a more stringent joining condition.

All comments

DataBlender

Hi mikaelhyensjo

1. A left join can be achieved by placing a union after the left and inner join outputs of the join tool:

You may find the linked documents useful for recreating database joins in Alteryx:

http://www.theinformationlab.co.uk/wp-content/uploads/2015/02/Joins-Tableau-custom-SQL-and-Alteryx-1024x679.png

http://www.theinformationlab.co.uk/2015/02/05/joining-data-tables-tableau-alteryx/

2. Assuming day number is simply the nth day of the year, you can achieve this in a formula tool as follows:

DateTimeFormat([DateField],"%j")

Please note that the output of this formula defaults to being a string, so you will need to convert it into a number afterwards if you would like to do any numerical operations on it.

As a further point, if you are looking to find the difference between two days, it is not necessary to convert these into nth days of years first, as it can be achieved through the following formula:

DateTimeDiff([DateField 1],[DateField 2],'days')

mikaelhyensjo

Thanks.

If I need to join with first Confirmed date and then Requested date I then need to do it twice (in two steps) ?

I need to have the day number for a specific division, thats way I need to fetch the number.

Will use the formula.

Mikael

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703