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

Create a new column based on two event dates

hash9
7 - Meteor

I have two different tables

Table 1:

EventStartEnd
AXX0XX3
BXX4XX5
AXX5XX6

 

table 2:

Start/EndDate
XX01-Jan
XX15-Jan
XX220-Feb
XX33-Mar
XX44-Jun
XX54-Jul

 

I need to create a new column in table 2 i.e Event. Event A starts at XX0 & ends before XX3. So, it should look at the "date" (Table2) and assign the Event as "A" for the respective "Start/End"(Table2).

 

My output should look like this.

Start/EndDateEvent
XX01-JanA
XX15-JanA
XX220-FebA
XX33-MarA
XX44-JunB
XX54-JulA

 

Please help.

8 REPLIES 8
Joe_Mako
12 - Quasar

One route is to generate records for all values in the range, and then join.

 

range join.png

 

There are other techniques if this does not scale for your situation, more details would help us explore other routes.

 

 

hash9
7 - Meteor

Thankyou. table2 'Start/End' are not exactly in the same range. For Example, ABC,DER,FER, etc. The dates attached to these codes are different. How do i go about this?

 

Start/EndDate
ABC1-Jan
CDE5-Jan
FER20-Feb
REF3-Mar
TEG4-Jun
VER4-Jul

 

Joe_Mako
12 - Quasar

How about an Append, set to allow for all appends (drop-down at bottom of tool configuration), and then a filter?

range join2.png

 

hash9
7 - Meteor

This works only when Start/End has number at the end. Have  attached the workflow here changing the "Start/End" column. 

Joe_Mako
12 - Quasar

Can you explain the logic you would use here for matching these?

 

How would you know that CDE is between ABC and XX3?

 

Maybe we need some data that more closely matches your real data, or I am misunderstanding your request.

hash9
7 - Meteor

Sorry for the confusion. I have changed the inputs to closely match my data.

 

Inputs 

EventStartEnd
AABCJUH
BABCCDE

 

Start/EndDate
ABC1-Jan
ABC1-Jan
CDE5-Jan
EFG20-Feb
HIJ3-Mar
JUH4-Jun

 

Logic: Event A should begin at the date of "ABC" and end before the date of "JUH" 

Output:

VenueStart/EndEvent
ABC1-JanA
ABC1-JanB
CDE5-JanA
EFG20-FebA
HIJ3-MarA
JUH4-Jun 

 

Joe_Mako
12 - Quasar

How about the attached, I made on of the operators < instead of <= and brought in any records that were not matched.

sample jm.png

hash9
7 - Meteor

Thankyou!

Labels