5 Replies Latest reply: Oct 7, 2016 3:06 AM by Sunny Talwar RSS

    Join not work out of the box but with a twist?

    Niko Suomi

      Hello community!


      I had a really odd obstacle on my way yesterday with QlikView, when the client was having some load problems with his QlikView application. The main problem was, that the application that once worked, now did not. After my investigation, the main reason for this "not working" thing was that the data did not load correctly for some reason any more.


      After a few test load, I saw that the main table did not "exist" during the joins directly. I made so called double load from the same data and then a join and after that everything worked just normally.


      Does someone in community now, what is the main reason why the application did not work anymore? Why did I have to make double load and then join? So what I modified in the load, was to add another LOAD statement, that loads all the same data that it did load from the previous statement.


      See the code below:


      PRODS:
      LOAD

      @1 AS prod_id,
      @2 AS prod_name

      FROM
      $(Path)prods\*
      (
      txt, utf8, no labels, delimiter is \x1, no quotes);

      join (PRODS)
      LOAD Distinct
      @3 AS prod_id,
      @4 AS prod_name
      FROM
      $(Path)orderitems\*
      (
      txt, utf8, no labels, delimiter is \x1, no quotes);

      //left join (PRODS)

      // this was previously done with straight join to the PRODS table, but now it did not work

      //
      PRODMAP
      :
      LOAD @1 AS prod_id,
      @3 AS slot_id

      FROM
      $(Path)productmap\*
      (
      txt, utf8, no labels, delimiter is \x1, no quotes);

      // when I do the join here and load from resident, everything works normally
      left join (PRODS)
      load * Resident PRODMAP;
      drop table PRODMAP;




      Cheers,

      Niko

        • Re: Join not work out of the box but with a twist?
          Sunny Talwar

          Are there multiple files at this location now? May be you had just one file previously and now it is more than one?

           

          PRODMAP:
          LOAD @1 AS prod_id,
          @3 AS slot_id
          FROM
          $(Path)
          productmap\*

            • Re: Join not work out of the box but with a twist?
              Niko Suomi

              Hello,

               

              Well in my good guess, because I don't really have the right answer right now. is that there has been multiple files before also. But I think I have to ask if that is the case. But how that can mess around with the Load & join ? Or should that be a for each function there in place and when there has been one file it just loaded the one. And if there has been many files, it just has loaded the first one of them?

               

              So now with the "double load" - we get all the files loaded to the table. I think I now understand a little bit better

               

               

               

              Cheers,

              Niko

                • Re: Join not work out of the box but with a twist?
                  Sunny Talwar

                  When you have multiple files, they auto-concatenate to form a single table (assuming all of them have the same set of fields). But if you are joining, the auto-concatenate action might not kick in. It tries to join the first excel file and once it does that, the other once won't join at all because of the left join.

                   

                  So I think it is work checking if there was only one file before and now you have multiple files.

                   

                  Also keep an eye on your other join

                   

                  join (PRODS)
                  LOAD Distinct
                  @3 AS prod_id,
                  @4 AS prod_name
                  FROM
                  $(Path)orderitems\*
                  (
                  txt, utf8, no labels, delimiter is \x1, no quotes)
                  ;


                  Check how many files are within this location