5 Replies Latest reply: Nov 20, 2017 4:52 AM by Petter Skjolden RSS

    Loading the columns if present

    Mukesh Baba Kodeboina

      Hi all,

      I have a small problem please help me out.

       

      I am connecting the Qlik Sense to the sql server to fetch the data. The table may have the n number of columns it may vary every time in the Sql server. When I load the data from Sql Server  using the Qlik load statement, When the column is present the values related to the column have to be retrieved other wise null has to be allocated to the column.

       

      Regards,

      Mukesh.

        • Re: Loading the columns if present
          Petter Skjolden

          A Qlik load script that demonstrates how it can be achieved:

           

          T1:
          LOAD * INLINE [
          A,B,C,D,E,F
          ];
          
          CONCATENATE (T1) LOAD *
          INLINE [
          B,F,G
          1,2, 3
          5,6, 7
          9,10, 11
          ];
          
          T2:
          LOAD A,B,C,D,E,F RESIDENT T1;
          DROP TABLE T1;
          

           

          To make it work with SQL Server you can replace line #7 to 12 like this:

           

          T1:
          LOAD * INLINE [
          A,B,C,D,E,F
          ];
          
          CONCATENATE (T1) LOAD *;
          SQL
            SELECT
              *
            FROM 
              dbo.aTable;
          
          
          T2:
          LOAD A,B,C,D,E,F RESIDENT T1;
          DROP TABLE T1;
          

           

          If the table that is read does not contain extra new columns and only have columns missing you could drop the lines #14 to 16.

           

          To avoid repeating the field names twice you could use a variable and $-sign expansion like this:

           

           

          vCols = 'A,B,C,D,E,F';
          
          T1:
          LOAD * INLINE [
          $(vCols)
          ];
          
          CONCATENATE (T1) LOAD *;
          SQL SELECT * FROM dbo.aTable;
          
          T2:
          LOAD $(vCols) RESIDENT T1;
          DROP TABLE T1;
          
          vCols=;