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\';


      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:






      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:





           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



        • Re: Load Script with flexible fields
          Chee Tiong Chua



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




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





          • 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,


            • Re: Load Script with flexible fields
              Jonathan Dienst



              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
                   load  itemno,
                   FROM $(Server)\Qvd\items.qvd (qvd);
                   load itemno,
                     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



              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 ...




                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


                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!