Count from a column joined by intervalmatch give different value from counting from a normal column

[#1]

Hi All,

I ran into a funny situation. Do you know why the result between the two formula is different?

Following is the script:

OracleTable:

Load

select

Col_A, Col_B,Col_C, Number from Oracle Table;

RangeTable:

Load * inline

Start, End, Group

1, 10, A

11, 20 B

21, 30 C

left Join

IntervalMatch(Number)

Load:

Start, End

Resident RangeTable;

Suppose there are 20000 rows returned from Oracle and all rows from the Oracle Table have successfully matched to the Range Table. I have a different results between the following two different expressions from the front-end.

Expression 1

Count(Col_A)   -- A column that is compulsory from the oracle table.

Result: 20000 -- This is correct

Expression 2

Count(Group) -- A column from the link table.

Result : 180 -- This is crazy and is unexpected.

As mentioned above, all rows from the Oracle Table are successfully matched to the range table, I am expecting the expression 2 should also return me 20000, but it only returned me 180.

Do you have any reason for this happened?

Thanks in advance!

[#2]

Peter, Jonathan and Ruben and Swuehl,

Do you mean that after executed the intervalmatch statemtn my range table only have 180 rows even it originally have three rows and it can matched with all rows in the Oracle Table.

If yes, than I cam clear now.

Thanks a lot!

Elim

[swuehl]

Well, we don't have access to all details, but you can check yourself:

Run your current script, open table view (CTRL-T) and hover over the table header. Numer of rows and some additional information should be displayed in a pop-up.

[Peter_Cammaert · Accepted solution]

Yes, because the LEFT JOIN joins a larger table of discrete Number/Start/End combinations to your RangeTable. You can inspect this larger table if you remove/comment the LEFT JOIN prefix and open the Table Viewer.

Indeed you will now find a synthetic key that is connecting fields from this larger table to the counterparts in other tables (new table for all of them, Oracle Table for Number, RangeTable for the others). This synthetic key is not dangerous, and as long as your combinations aren't too many (for example while developing your document) you can leave it there. It will not negatively influence associativity.

Based on your code (almost exact copy), I did two tests. When my OracleTable contains three discrete values in field Number, I get this:

If on the other hand I enter 5 discrete values (same number of rows) in my OracleTable, I get this:

If your OrcleTable has a Number field with 180 different values, you'll get a RangeTable with 180 entries after the JOIN. You want to keep only 3 entries in RangeTable? Then omit the LEFT JOIN and let the new table + synthetic key

take care of all combinations of Number and Start/End range limits.

Best,

Peter