Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

How to calculate qty days between periods in the same month

igorfv
8 - Asteroid

Hi guys,

 

I have a challenge to calculate qty days between periods in the same month. See the example below. Could help me, please?

 

Table 1
Doc number (Key)Start Date
12023-01-01
12023-01-15
12023-01-25
 
Table 2
Doc number (Key)End Date
12023-01-10
12023-01-20
12023-01-30

 

Result
Doc number (Key)Start DateEnd DateQty Days
12023-01-012023-01-109
12023-01-152023-01-205
12023-01-252023-01-305

 

Result
Doc number (Key)Qty Days
119

 

 

Note: It's possible exists many Start and End dates in the same month or not.

 

 

Thanks,

Igor

3 REPLIES 3
chandler-gjino
Alteryx
Alteryx

take a look at this workflow and see if it helps
without a unique key between the two tables, it'll be hard to do a true join.
If we joined on doc number, because it's repeated, we'd come out with 9 records (each combination of start and end) -- however you could have a series of logic to remove the duplicates. 

 

I have one option joining on position, if that's possible for your needs. and the other option joining on doc num but removing the duplicates at the end

rzdodson
12 - Quasar

@igorfv posting one way to do this (see below and attached).

Solution.png

 

As an aside, I would recommend that there is some additional level of detail in the Doc number (Key) field as it can get confusing if you are attempting to join on record position. But, for this use case, the solution gets to the desired end state.

igorfv
8 - Asteroid

Hi Guys,

 

Great! It's worked. Thank you for your support.

 

Igor Valle

Labels
Top Solution Authors