Thu Jul 07, 2022 7:24 am
Login Register Lost Password? Contact Us

Please Note: The HPCC Systems forums are moving to Stack Overflow. We invite you to post your questions on Stack Overflow utilizing the tag hpcc-ecl (https://stackoverflow.com/search?tab=newest&q=hpcc-ecl). This legacy forum will be active and monitored during our transition to Stack Overflow but will become read only beginning September 1, 2022.



Conditional logic in join criteria

Comments and questions related to the Enterprise Control Language

Wed Jul 21, 2021 9:33 pm Change Time Zone

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

Thu Jul 22, 2021 12:29 pm Change Time Zone

Janet,

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
Community Advisory Board Member
 
Posts: 1619
Joined: Wed Oct 26, 2011 7:40 pm

Thu Jul 22, 2021 1:51 pm Change Time Zone

Apparently I over-complicated it. The solution worked. Thanks!
janet.anderson
 
Posts: 52
Joined: Wed Jul 17, 2013 5:31 pm

Thu Jul 22, 2021 1:51 pm Change Time Zone

Apparently I over-complicated it. The solution worked. Thanks!
janet.anderson
 
Posts: 52
Joined: Wed Jul 17, 2013 5:31 pm


Return to ECL

Who is online

Users browsing this forum: No registered users and 1 guest

cron