topic Why is this Right Join not working ? in App Development

Why is this Right Join not working ?

Derek_T — Tue, 12 Jul 2022 15:52:43 GMT

I don't understand why the joins below are not working properly.
Here are the 3 tables I'm using with the expected and actual results, and the code section below.

Here is the code and how the table are joined together:

NoConcatenate
Resulting_Table:
LOAD Distinct
msn,
operator
Resident Table1;

Left Join
LOAD Distinct
msn,
Date
Resident Table2;

Right Join
LOAD Distinct
Date
Resident Calendar;

By the "law of Join" the last Right Join should keep all the entries in Calendar table and just report the matching entries in the previous table. But I don't obtain the expected results, in fact I get the same results if I comment the Right Join section.

PS: Just in case I've done the same thing by joining the first 2 tables in a "temp" table and use that temp table to join with the calendar but the results is still the same.

Thanks !

Re: Why is this Right Join not working ?

Or — Tue, 12 Jul 2022 17:05:40 GMT

Your expected result is what I get when I run your code, replacing residents with inlines, other than it includes 01-01 (which I think you mistakenly left out):

Resulting_Table:
LOAD Distinct
msn,
operator
inline [
msn, operator
1, A
2, B
3, B
4, C];

Left Join
LOAD Distinct
msn,
Date
inline [
msn, Date
1, 5
2, 10
3, 15
4, 20
];

Right Join
LOAD Distinct
Date
INLINE [
Date
1
2
3
4
5
6
];

Possibly the issue is that you didn't drop the original resident tables?

Re: Why is this Right Join not working ?

Derek_T — Tue, 12 Jul 2022 19:04:51 GMT

I cannot drop the resident table unfortunately, they are part of the data model. I'm surprised that this play a role I have other tables build along the same line and they work it's just this one I don't get where the issue is coming from

Re: Why is this Right Join not working ?

Or — Tue, 12 Jul 2022 21:34:47 GMT

If your resident tables are still there, then you're seeing the values from those tables, not the values from your new left/right joined tables (or rather, you're seeing the combination). In the below I've only loaded the first table as resident (and kept the original) but in your case it's actually all three.

ResidentTable:
LOAD Distinct
msn,
operator
inline [
msn, operator
1, A
2, B
3, B
4, C];

SecondTable:
NoConcatenate Load * Resident ResidentTable;

Left Join
LOAD Distinct
msn,
Date
inline [
msn, Date
1, 5
2, 10
3, 15
4, 20
];

Right Join
LOAD Distinct
Date
INLINE [
Date
1
2
3
4
5
6
];