Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Is the date between those 2 dates

david20100
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

 

4 REPLIES 4
cpituley
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

david20100
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
cpituley
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

david20100
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