5 Replies Latest reply: Oct 21, 2009 10:59 AM by Joachim Rogginer RSS

    Left Join Fails

    rey-man

      Hi,

       

      I've got a simple script which shows my problem. (see attachment)

      I've got 2 tables: 1 with intake and 1 with outgoing.

      This is based on a date.

      I want to merge these 2 based on date.

      So I load in 1, then the next and finally I left join it.

      Next, i take the next date and do the same, but then it fails.

      But the code is identical, only the date is different.

      I do it this way, because I have to calculate with some other values in those tabel to create a sum.

      To simplify I took the summed tables as example.

      Hope u can help me.

      Rgds,

      Rey-man

       

        • Left Join Fails
          Joachim Rogginer

          If you insert an 'exit script;' before the left join and after a reload check the table structure you will see that you have a synkey consisting of Datum AND Outgoing.
          In the Outgoing table you have at that time one Row:

          Date Outgoing
          2-4-2009 500

          In OpenOrderTemp you have two rows:

          Date Outgoing Intake
          1-4-2009 1203 20102
          2-4-2009 2010

          Now, a left join on OpenOrderTemp takes the full data set of OpenOrderTemp and tries to join with Outgoing. As a join on Datum AND Outgoing CANNOT be made, the row of Outgoing 500 gets lost! The mistake as said is that Outgoing is used as key!! That's my interpretation for why it does not work.

          Rgds,
          Joachim

           

          • Left Join Fails
            Johannes Sunden

            Hi Rey-man,

            I'm not exactly sure what your problem is but I think I know what's causing it.

            If you load two identical tables, in terms of number of fields and field names, QlikView will automatically concatenate them.

            When you do a Load * Resident xxx for example you will end up just doubling the rows in the table. I'm not sure if the join will be correctly applied there.

            Either add a dummy field at some point like 1 as dummy, or use the NOCONCATENATE command which is described in the F1-help.

            • Left Join Fails
              Joachim Rogginer

              Find attached my solution. Perhaps there are more elegant ways, but I couldn't figure one out right now. Nevertheless, perhaps it helps.

              Rgds,
              Joachim