Compare (>=, <=) multiple fields from different tables in script editor

[BasC]

I have 2 tables (here they are with only the relevant bits):

dosierZending:
Load
  zending,
  if(IsNull(pcZone),tszolo,pcZone) as pcZone,
  zendingJaar,
  brGewZending
From ##### (qvd);

zztarl:
LOAD
    pcZone,
    Year(tariefDatumTot) as zendingJaar,
    tariefGewVan,
    tariefGewTot,
    tariefBedrag
From ##### (qvd);

pcZone and zendingJaar are the same in both tables. brGewZending is a value and needs to be compared to:

pcZone, zendingJaar, tariefGewVan and tariefGewTot. Then it needs to give tariefBedrag as a result. I then want to left join tariefBedrag to dosierZending. So considering it should automatically match the pcZone and zendingJaar, this would be the formula:
if(brGewZending  >= tariefGewVan and brGewZending  <= tariefGewTot, tariefBedrag)

I can't just join dosierZending and zztarl though, as each tariefGewVan and tariefGewTot has 17 values, so it would increase dosierZending 17-fold.

So I don't know how to do this in the editor. If anyone can help me get further, I very much appreciate it. Thanks in advance!

[GaryGiles]

Are the 17 values all the same or can they be unique?  Are you looking to compare the sum of tariefGewVan and tariefGewTot for a pcZone, zendingJaar combination to brGewZending?  Or, are you wanting tariefBedrag is any of the combinations meet the criteria?

[BasC]

Thanks for your reply!

The 17 values are unique (per year per pcZone). Here's an example:

So, if pcZone = ZN1, zendingJaar (year) = 2020 and brGewZending is between tariefGewVan = 9.0 and tariefGewTot = 16.999, then it should give that tariefBedrag.

So all the combinations should be unique and there's no overlap. So no need for a sum.

[GaryGiles]

Okay, try this. 

Add the following to you rload script after your 2 tables loads:

Noconcatenate

TempdosierZending:
Load
  zending,
  if(IsNull(pcZone),tszolo,pcZone) as pcZone,
  zendingJaar,
  brGewZending
From ##### (qvd);

Left Join (TempdosierZending)

 Load   pcZone,
    Year(tariefDatumTot) as zendingJaar,
    tariefGewVan,
    tariefGewTot,
    tariefBedrag

Resident zztarl;

Left Join (dosierZending)

Load zending,

         pcZone,

         tariefBedrag as NewtariefBedrag

Resident TempdosierZending

Where brGweZending >= tariefGewVan

     and brGewZending <= tariefGewTot;

Drop table TempdosierZending;

[BasC · Accepted solution]

Thank you so much! I haven't tried it, but it looks like a solid solution.

But I did manage to do it in a different way. A contact of mine told me to do it with IntervalMatch. This is how I did it:

At first I tried

Inner Join 
IntervalMatch ( brGewZending, pcZone, zendingJaar )
Load
    tariefGewVan,
    tariefGewTot,
    pcZone, zendingJaar
Resident zztarl;

That didn't work. So I put pcZone and zendingJaar together as pcZoneZendingJaar in both dosierZending and zztarl.

The result is this:

dosierZending:
Load
    zending,
    if(IsNull(pcZone),tszolo,pcZone) & zendingJaar 
        as pcZoneZendingJaar
    brGewZending
From ##### (qvd);

zztarl:
LOAD
    pcZone&Year(tariefDatumTot) as pcZoneZendingJaar,
    tariefGewVan,
    tariefGewTot,
    tariefBedrag
From ##### (qvd);

Inner Join (zztarl)
IntervalMatch ( brGewZending, pcZoneZendingJaar )
Load
    tariefGewVan,
    tariefGewTot,
    pcZoneZendingJaar
Resident zztarl;

Left Join (dosierZending)
Load
    brGewZending,
    tariefBedrag,
    tariefGewVan,
    tariefGewTot,
    pcZoneZendingJaar
Resident zztarl
Drop Table zztarl;
Drop Field pcZoneZendingJaar;

After the Inner Join I got a synthetic key, so I just left joined it to dosierZending and dropped zztarl.

This works great, as you can see:

All the brGewZending fits into the right tariefGewVan and tariefGewTot.