Multiple join does not work, why?

[marko_rakar]

I have a situation where I load couple of tables which need to be either concatenated or left join.

Here is a simplified case:

Table1

LOAD * INLINE [

    F1, F2

    123, 1

    123, 2

    123, 3

];

Needs to be concatenated to table2

LOAD * INLINE [

    F1, F2

    123, 5

    123, 6

    123, 7

];

And this happens correctly (field names are recognized and tables are concatenated whether I explicitly ask for it or not) then, I make my first left join:

LOAD * INLINE [
    F2, F3
    1, 10
    23, 1234
    333, 1234
];

And this join happens correctly as well and to the concatenated table1 I get in the field F3 in the row which have value "1" (in this case, frist row of the new made table)

Now, I need additional left join

LOAD * INLINE [

    F1, F3

    123, 123

    23, 222

    323, 333

];

Idea of this one is that in every row where I find value 123 I should add new colum with a new value in a column F3. Technicaly, with inline example I am not showing correctly what is happening and my script is:

LOAD F3 as [F1],

          F3 as F1

FROM

[f-database.qvd] (qvd);

So, as a bottom line, my first two tables concatenate correctly, and my first join is also correct but the second join simply does not work at all.

Can anyone explain why?

[Gysbert_Wassenaar]

The second left join is matching records on the combination of F1 and F3, not just F1. Your left table does not have any records with matching values for both F1 and F3 in the right side table so nothing happens.

[swuehl]

Because in your second join F1 and F3 are key fields and your join will look for matching values in both key fields (and since you are not adding additional field values, what information should be added then?).

[marko_rakar]

Both left joins are supposed to come from same database (and are actually coming from single database)

BUT, I need to join on two criteria, so my thinking was that:

1. First join will try to find matching fields F2 (which exist in both original table and table which is left joined) and will add field F3 to the table for matching rows
2. Second join will try to find maching fields F1 in first database and to appropriate rows will add values from/to F3 field.

[swuehl]

So you want something like this:

1) Create F3 based on F2 field.

2) For the records where 1) haven't resulted in a match, use a lookup from F1 field.

If you want to add only one field in total, use a MAPPING aproach instead of joins.

Load your two lookup tables as mapping tables first the create your concatenated table and instantly add F3 field by use of applymap() (or nested applymaps() if you want above logic).

MAP1:

MAPPING LOAD F2, F3 FROM ...

MAP2:

MAPPING LOAD F1, F3 FROM ...

LOAD

     Applymap('MAP1', F2, Applymap('MAP2',F1) ) as F3,

     F1,

     F2,

....

[marko_rakar]

well, this is closer and I understand the principle but it seems it does not work.

Here is another piece of code which is basically the same as above but loads from data files:

MAP1:

MAPPING LOAD  F2,

              F3

FROM

[MAP1.qvd] (qvd);

MAP2:

MAPPING LOAD  F1,

              F3

FROM

[MAP2.qvd] (qvd);

//----------------------------

Directory;

LOAD F1,

    F2,

   ApplyMap('MAP1', F2, ApplyMap('MAP2', F1, null())) as F3

FROM

[total.qvd]

(qvd);

The thing is that I need to match field F2 from my data table with matching row in MAP1 and then add F3 field; if there is no match then I have to try to find if the field F1 has a match in MAP2 and then if there is a match add to field F3 and if there is no match then write down null.

But it does not work.

p.s. please note that in my first code example I have concatenated two tables; in this two examples I have already done that and I am loading single database.

[swuehl]

What do you mean with 'does not work'? Error message? Results not as expected?

Would be easier if you could post some sample lines of data.

Using a mapping approach as well as joining tables need matching values for the mapping / joining.

[swuehl]

Ah, you are using QVD as input to your Mapping loads:

Warning on using QVD for Mapping Load

(though I think this should have been fixed in latest releases ...?)

[marko_rakar]

uh, did not know that (back to scripting editor)

[marko_rakar]

sorry, I was not clear

there is no error, it is just that I can achieve same result like I had it with first left join (in my first example) and I am unable to achieve my second left join

my tables are much more complex, I will try to extract exactly fields I need for this mapping