Loading the columns if present

[mukesh19]

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.

[petter]

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=;

[mukesh19]

Hi petter this sounds good, but I need to derive some derived columns from the base columns that are obtained from the sql. I can achieve this using with resident load. But I want to know can I have the alternative for this

thanks,

mukesh

[petter]

The SQL that you put in can contain any valid SQL Server T-SQL syntax. So you can derive columns from the base columns directly in the SQL if you want.

[mukesh19]

No I want to derive those in the qlik only. When I load the data in the Qlik if the column is not present it will return error. I would like to know if there is any inbuilt function in the qlik so that it automatically passes the null to the column if the column is not present.  So, that there wont be any problem in deriving the additional columns.

Thanks

[petter]

There is no inbuilt function in Qlik to automatically pass null values if the column is not present in the source table.

That is why a CONCATENATE LOAD is necessary to achieve exactly that.

I can't see why it is not possible to derive additional columns even in the LOAD statment if not in the SQL statement.