9 Replies Latest reply: May 2, 2014 10:10 AM by Marko Rakar RSS

    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:



      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


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

        • Re: Multiple join does not work, 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.

          • Re: Multiple join does not work, why?
            Stefan Wühl

            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?).

              • Re: Multiple join does not work, why?
                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.
                  • Re: Multiple join does not work, why?
                    Stefan Wühl

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



                    MAPPING LOAD F2, F3 FROM ...



                    MAPPING LOAD F1, F3 FROM ...



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