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

[mikaelhyensjo]

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 ?

[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-1...

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

[DataBlender · Accepted solution]

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.