7 Replies Latest reply: Oct 10, 2012 4:47 AM by Robert Svebeck RSS

    Load Script with flexible fields

    Robert Svebeck

      I want to create a Qlikview document that should be able to load data from two different datasources, depending on a paramenter.

       

       

      My script starts like this:

       

      SET ProdTest = 'T';

      if $(ProdTest) = 'T' then

      SET Server = '\\w2k3qw2\';

      else

      SET Server = '\\w2k8qw4\';

      end if

       

      So I can easily change the root path from a "test" environment to a "production" envirionment.

       

      Now, later in the script, when loading the actual data I use this method:

       

      items:

      load 

           itemno,

           itemname

      FROM $(Server)\Qvd\items.qvd (qvd);

       

      This works great.

       

      Now to my dilemma:

       

      The QVD in the two paths are not completely the same, the test QVD will contain columns that the prod QVD does not have.

       

      So how can I write a script that can handle this?

       

      I have tried:

      items:

      load 

           itemno,

           itemname,

           if('$(ProdTest)'='T',NewTestField,'') as NewTestField

      FROM $(Server)\Qvd\items.qvd (qvd);

       

      But it seems to not work. I dont get any error, but the field NewTestField is always blank, both for test and prod QVD.So it seems my if(...) does not work.

       

      Any help appreciated.

       

      Best Regards

       

      Robert

        • Re: Load Script with flexible fields
          Chee Tiong Chua

          Hi,

           

          Remove the single quotations that wrap the '$(ProdText)' in your if statement, change it from '$(ProdText)' to $(ProdText),

           

          like

           

          if($(ProdTest)='T',NewTestField,'') as NewTestField


          Regards,

          Tiong

           

           


          • Re: Load Script with flexible fields
            Stefan Wühl

            I think you need to use the If..then..elseif..else..end if script statements to control the flow in your script.

             

            I don't think you can use these within the load, so you need to create two loads, and control which load to use using

            If..then..elseif..else..end if

             

            Hope this helps,

            Stefan

            • Re: Load Script with flexible fields
              Jonathan Dienst

              Robert

               

              You cant do that I am afraid, the present of NewTestField will trigger an error in the load even if you are not using it. You can split this into two load expressions like this:]

               

              If ProdTest = 'T' Then
              
                   items:
                   load  itemno,
                     itemname,
                     NewTestField
                   FROM $(Server)\Qvd\items.qvd (qvd);
              
              Else
              
                   items:
                   load itemno,
                     itemname,
                     null() As NewTestField
                   FROM $(Server)\Qvd\items.qvd (qvd);
              
              End If
              

               

              The null() is to ensure that the two load statements auto concatenate.

               

              Hope that helps

              Jonathan

               

              PS you could alos use LOAD * FROM .... to load all the fields, but you may have concatenation issues.

              • Re: Load Script with flexible fields
                Dave Riley

                Try loading the script into a variable similar to this example ...

                 

                 

                 

                OriginalData:
                LOAD * INLINE [
                itemno, itemname, TestField
                A, Apples, Cooking Apples
                B, Bananas, Small Bananas
                C, Carrots, Baby Carrots
                D, Duck, Duck a l'Orange
                E, Eggs, Easter Eggs];

                 

                LET ProdTest = ', TestField as TestField_new';
                //LET ProdTest = '';

                 

                LET vLoadScript =
                'itemno as itemno_new,
                itemname as itemname_new' &
                '$(ProdTest)' &
                ' resident OriginalData'; // note preceding space

                 

                items:
                LOAD $(vLoadScript) ;

                  • Re: Load Script with flexible fields
                    Robert Svebeck

                    This is the best solution. But I was hoping for a simple elegant solution and there seem to be none. All those solutions results in a script that gets complicated to read when I have a document that has 10 tables and all of them need to be able to be read from two different sources. Thanks for all help though! Appreciate it!

                     

                     

                    //Robert