Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Is the date between those 2 dates

Highlighted
8 - Asteroid

I have some product, and the date they have been created

ProductProd-End
CA-2002015-04-07
CA-2002015-04-07
CA-2002015-04-07
CA-2002015-04-07
CA-2002015-04-07
CA-2002015-05-22
CA-2002015-05-22
CA-2002015-05-22
CA-2002015-05-22
CA-2002015-05-22
CA-2002015-06-15

 

I also have a DB that returns me a value (Time)  if the production end date (Prod-End) is between the Start and End dates

 

ProductTimeStartEnd
CA-20051999-01-012015-10-04
CA-200112015-10-052016-11-02
CA-200152016-11-032017-12-05
CA-200122017-12-062018-06-23
CA-200102018-06-24

2099-09-16

 

 

I dont know if it's because my brain is stuck in flu i can't figure how to make it works

 

Highlighted
8 - Asteroid

Hi @david20100

 

Looks like you want to start out with an inner join on the two data sets with [Product] = [Product]. 

 

Then use the multifield formula to say (for a new named field)

IF [Prod-End] > [Start] and [Prod-End] < [End] THEN 0 ELSE 1 ENDIF

 

This should flag all product line entries that were created outside of the date range with a "1". 

 

Hope this helps. 

 

EDIT: You can also use the filter function after the join and filter the date based on the above stated criteria.

 

Thanks,

Cameron

8 - Asteroid

Maybe i'm not clear in my explanation

 

For this example

ProductProd-End
CA-2002015-04-07

 

It should return me 5 for that entry because its between those 2 dates

ProductTimeStartEnd
CA-20051999-01-012015-10-04
Highlighted
8 - Asteroid

Hi @david20100

 

My apologies, I misunderstood. 

 

The following change should accomplish what you are looking for: 

 

IF [Prod-End] > [Start] and [Prod-End] < [End] THEN [Time] ELSE 0 ENDIF

 

This will return 0 when it's not between those 2 dates and the value in the [Time] field when it is. Feel free to replace 0 with whatever you want to show up when it isn't between the date range.

Let me know. 

 

Thanks,

Cam

Highlighted
8 - Asteroid

Thanks that's a good idea, after that formula i applied a filter to remove all 0 to keep only the good data

 

thanks again

Labels