2 Replies Latest reply: Apr 14, 2017 5:42 PM by Jatinder Singh RSS

    Preceding load for concatenate (no resident)

    Jessica Tung

      Hello, I am looking for "preceding load" methodology to force concatenation between two tables of different fields, and then calculate based on the concatenated table. I know how to do it in resident and drop table, and I want to try preceding load, imagining it would be faster and would use less memory. But with the script below, it only populates [PL MARKER] for the first portion. How to do preceding load correctly?? Thanks.


      PRECLOAD:
      LOAD *,
      'PL'
      as [PL MARKER];


      FIRST:
      LOAD * INLINE
      [Customer, Account, Balance, ExtraFieldJ
      A,A1,10,J
      A,A2,20,J
      A,A3,30,J
      B,B1,100,J
      B,B2,200,J
      B,B3,300,J]
      ;
      Concatenate
      LOAD * INLINE
      [Customer, Account, Balance, ExtraFieldS
      C,C1,10,S
      C,C2,20,S
      C,C3,30,S
      D,D1,100,S
      D,D2,200,S
      D,D3,300,S]
      ;


      Customer

      Account

      Balance

      ExtraFieldJ

      PL MARKER

      ExtraFieldS

      A

      A1

      10

      J

      PL

       

      A

      A2

      20

      J

      PL

       

      A

      A3

      30

      J

      PL

       

      B

      B1

      100

      J

      PL

       

      B

      B2

      200

      J

      PL

       

      B

      B3

      300

      J

      PL

       

      C

      C1

      10

       

       

      S

      C

      C2

      20

       

       

      S

      C

      C3

      30

       

       

      S

      D

      D1

      100

       

       

      S

      D

      D2

      200

       

       

      S

      D

      D3

      300

       

       

      S

       

        • Re: Preceding load for concatenate (no resident)
          Muñoz Héctor

          Hi Jessica,

          Do the following:

           

          PRECLOAD:

          LOAD *,

          'PL' as [PL MARKER];

          LOAD * INLINE

          [Customer, Account, Balance, ExtraFieldJ

          A,A1,10,J

          A,A2,20,J

          A,A3,30,J

          B,B1,100,J

          B,B2,200,J

          B,B3,300,J];

           

           

          Concatenate (PRECLOAD)

          LOAD *,

          'PL' as [PL MARKER];

          LOAD * INLINE

          [Customer, Account, Balance, ExtraFieldS

          C,C1,10,S

          C,C2,20,S

          C,C3,30,S

          D,D1,100,S

          D,D2,200,S

          D,D3,300,S];

           

          Regards,
          H

          • Re: Preceding load for concatenate (no resident)
            Jatinder Singh

            Hi Jessica,

            Regarding your question, the Preceding Load will only work for the related table (Table 1). Qlikview loads data in the script's sequence order, it will process data in the following sequence in your case:

            1. Load Table 1
            2. Preceding load of Table 1
            3. Load Table 2
            4. Concatenate TABLE 1 with TABLE 2.

             

            Depending on your requirements, if you just have to add only one hard coded flag ('PL' as [PL MARKER];) then I would suggest Hector Muñoz approach is perfect, but there would be a duplication of code if you have more fields and in case if you have to derive calculated fields based on the concatenated table then you must use the approach you have mentioned "RESIDENT LOAD and DROP TABLE".

             

            Best regards,
            JSI