5 Replies Latest reply: Apr 18, 2013 3:43 PM by Puneet Lakhanpal RSS

    Join on table containing input fields - a bug ?

      Hi,

       

      I have a simple table containing an input field. When that table is used as a source for join on another table, the data inside the input field changes to row numbers. Please see the attached qvw and let me know if there is a workaround possible to maintain the data along with the join.

       

      Here's the script:

       

      INPUTFIELD ProductThreshold;

       

       

      ProductThresholds:

      LOAD * INLINE [

      ProductName, ProductThreshold

      P1, -0.025

      P2, -0.5

      ];

       

       

      ProductDim:

      LOAD * INLINE [

      ProductName

      P1

      P2

      P3

      ];

       

       

      LEFT JOIN(ProductDim)

      LOAD

      *

      Resident

      ProductThresholds;

       

       

      DROP TABLE ProductThresholds;

        • Re: Join on table containing input fields - a bug ?
          Gysbert Wassenaar

          As you noticed a join will change the table. A resident load from the table has the same effect. The workaround is to store the table into a qvd directly after creating it, then dropping it and at the end of the script load the table again from the qvd. But don't join it, that will simply disable the input field again. If you need it in one table then first create the joined table, store that table into a qvd and drop it, then define the input field and load the table from the qvd.

            • Re: Join on table containing input fields - a bug ?

              Hi Gysbert,

               

              Thanks for your response. However, which table are you referring to ? I tried the following two approaches but both did not work.

               

              I tried

               

              1. Storing ProductThresholds into qvd and doing a left join on ProductDim directly from ProductThresholds.qvd

               

              INPUTFIELD ProductThreshold;

               

              ProductThresholds:

              LOAD * INLINE [

              ProductName, ProductThreshold

              P1, -0.025

              P2, -0.5

              ];

               

              ProductDim:

              LOAD * INLINE [

              ProductName

              P1

              P2

              P3

              ];

                 

              STORE ProductThresholds INTO ProductThresholds.qvd;

              DROP TABLE ProductThresholds;

                 

              LEFT JOIN(ProductDim)

              LOAD *

              From

              ProductThresholds.qvd (qvd);

               

              2. I also tried storing ProductDim after joining with ProductThresholds into qvd, dropping it and loading it back like this -

               

              INPUTFIELD ProductThreshold;

               

              ProductThresholds:

              LOAD * INLINE [

              ProductName, ProductThreshold

              P1, -0.025

              P2, -0.5

              ];

               

              ProductDim:

              LOAD * INLINE [

              ProductName

              P1

              P2

              P3

              ];

               

              LEFT JOIN(ProductDim)

              LOAD *

              Resident

              ProductThresholds;

               

              STORE ProductDim INTO ProductDim.qvd;

              DROP TABLE ProductDim;

               

              ProductDim:

              LOAD *

              FROM

              ProductDim.qvd(qvd);

                • Re: Join on table containing input fields - a bug ?

                  Hi Gysbert,

                   

                  Thanks for the idea.

                   

                  As you suggested, the following worked -

                   

                  ProductThresholds:

                  LOAD * INLINE [

                  ProductName, ProductThreshold

                  P1, -0.025

                  P2, -0.5

                  ];

                   

                   

                  ProductDim:

                  LOAD * INLINE [

                  ProductName

                  P1

                  P2

                  P3

                  ];

                   

                   

                   

                   

                   

                   

                  LEFT JOIN(ProductDim)

                  LOAD

                  *

                  Resident

                  ProductThresholds;

                   

                   

                  STORE ProductDim INTO ProductDim.qvd;

                  DROP TABLE ProductDim;

                   

                   

                  INPUTFIELD ProductThreshold;

                   

                   

                  ProductDim:

                  LOAD

                  Distinct *

                  FROM

                  ProductDim.qvd(qvd);

              • Re: Join on table containing input fields - a bug ?

                Just a thought.

                 

                If you use just this:

                INPUTFIELD ProductThreshold;

                 

                 

                ProductThresholds:

                LOAD * INLINE [

                ProductName, ProductThreshold

                P1, -0.025

                P2, -0.5

                ];

                 

                 

                ProductDim:

                LOAD * INLINE [

                ProductName

                P1

                P2

                P3

                ];

                 

                You will have two tables but the desired results