Conditional logic in join criteria
My team has a table of criteria that we want excluded from a dataset. It has a layout like {STRING CarrierCode, SET OF STRING StateList, STRING8 BeginDate, STRING8 EndDate}. So I want to join my exclude table to the dataset with criterial like
The catch is that the StateList can be something like ['All'] that implies there should be no join criteria for state. So what I would really like is more like
Except that code doesn't work. Can you suggest a way to make conditional join criteria work?
- Code: Select all
LEFT.carrier = RIGHT.CarrierCode
AND LEFT.state in RIGHT.StateList
AND LEFT.date BETWEEN RIGHT.BeginDate AND RIGHT.EndDate
The catch is that the StateList can be something like ['All'] that implies there should be no join criteria for state. So what I would really like is more like
- Code: Select all
LEFT.carrier = RIGHT.CarrierCode
#IF RIGHT.statelist[1] <> 'All'
AND LEFT.state in RIGHT.StateList
#END
AND LEFT.date BETWEEN RIGHT.BeginDate AND RIGHT.EndDate
Except that code doesn't work. Can you suggest a way to make conditional join criteria work?
- janet.anderson
- Posts: 52
- Joined: Wed Jul 17, 2013 5:31 pm
Janet,
Try it like this:
Standard IF..THEN..ELSE with BOOLEAN expressions as the true/false returns.
HTH,
Richard
Try it like this:
- Code: Select all
JOIN(lds,rds,
IF(RIGHT.statelist[1] = 'All',
LEFT.carrier = RIGHT.CarrierCode
AND LEFT.date BETWEEN RIGHT.BeginDate AND RIGHT.EndDate,
LEFT.carrier = RIGHT.CarrierCode
AND LEFT.state in RIGHT.StateList
AND LEFT.date BETWEEN RIGHT.BeginDate AND RIGHT.EndDate));
Standard IF..THEN..ELSE with BOOLEAN expressions as the true/false returns.
HTH,
Richard
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
Apparently I over-complicated it. The solution worked. Thanks!
- janet.anderson
- Posts: 52
- Joined: Wed Jul 17, 2013 5:31 pm
Apparently I over-complicated it. The solution worked. Thanks!
- janet.anderson
- Posts: 52
- Joined: Wed Jul 17, 2013 5:31 pm
4 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 1 guest