Search code examples
qliksense

Concat in loadscript between SQL statements

Hej folks,

it is driving me crazy. I'll try to concat some values from one table to use it in a where clause in another statement. It's like this script.

LIB CONNECT TO 'MSSQLSERVER';

TempTab:
Load KST;
SQL SELECT KST FROM vKST WHERE Region = 'Driver';

Let Test = Concat(distinct KST, ',');

drop Table TempTab;

// ...

LIB CONNECT TO 'ORACLESERVER';

Foo:
Load *;
SQL SELECT Value FROM KSTvalues WHERE KST IN ($(Test));

My problem is that the variable "Test" is only calculated to null. Has anyone a working idea for this?

Solution

  • In this case Concat function should be used in context of a table in order to get all the values from a field.

    So to get all values you'll have to load them in a temp table first and in it to perform the concatenation. And then use variable to get the resulted field value.

    Have a look at the script below. The concatenation is performed in TempTable and then using peek function to get the value of ConcatField into vConcatValues variable (im dropping the TempTable at the end because its not needed once the variable is populated)

    TempTable will have the following content:

    Table content

    And vConcatValues will be:

    Variable content

    RawData:
Load * inline [
Values
Value1
Value1
Value2
Value3
Value4
Value5
];


TempTable:
Load
  Concat(distinct Values, ',') as ConcatField
Resident
  RawData
;

let vConcatValues = peek('ConcatField');

// We dont need the TempTable anymore and can be dropped
Drop Table TempTable;

    P.S. probably the sql clause will raise an error, because the values will not be defined as strings. In this case you can use something like this:

    TempTable:
Load
  Concat(distinct Values, '","') as ConcatField
Resident
  Raw
;

    Using "," as separator will result in Value1","Value2","Value3","Value4", "Value5 (see the missing " in front and in the end)

    We'll have to tweak the variable a bit to fix this:

    let vConcatValues = '"' & peek('ConcatField') & '"';

    And the result then will be:

    "Value1","Value2","Value3","Value4", "Value5"