Case SQL Load from qlikview

[lior_malcka]

Hello,

I am trying to load tables with for loop, but every table is using different fields, so I created an excel table that holds all the fields names but I encountered another issue that not all tables need all the values from the excel table so I tried to use many ways (case, for and more)  but something always fails.

Can someone point me to the problem?

hope you will be able to understand my English

I am using Oracle DB

thanks

vDB= //holds all Databases Names

HebConvFields:

LOAD Table,

     Pkey1,

     Pkey2,

     ConvField1,

     ConvField2,

     CaseSwitch

FROM [..\1_Resources\HebConvertTables.xlsx] (ooxml, embedded labels, table is Tables);

LET NumRows=NoOfRows('HebConvFields');

LET i=0;

Do while  i<=$(NumRows)-1

  LET vHebTable=Peek('Table',$(i));

  LET vHebPkey1=Peek('Pkey1',$(i));

  LET vHebPkey2=Peek('Pkey2',$(i));

  LET vHebConvField1=Peek('ConvField1',$(i));

  LET vHebConvField2=Peek('ConvField2',$(i));

   LET vCaseSwitch=Peek('CaseSwitch',$(i));

  LET i = i + 1;

For Each vDB in $(vComp)

$(vHebTable):

LOAD *,

Left('$(vDB)',INDEX('$(vDB)','$')-1) as DATABASE,

SQL SELECT

If  vCaseSwitch = 1 THEN

   SQL SELECT

   $(vHebPkey1),

   tabula.tabulaf.hebconvert(tabula.tabulaf.revstr("$(vDB)$(vHebTable)".$(vHebConvField1))) AS $(vHebConvField1)_HEBCOV

   FROM TABULA."$(vDB)$(vHebTable)";

Else If vCaseSwitch = 2 THEN

SQL SELECT

   $(vHebPkey1),

   $(vHebPkey2),

   tabula.tabulaf.hebconvert(tabula.tabulaf.revstr("$(vDB)$(vHebTable)".$(vHebConvField1))) AS $(vHebConvField1)_HEBCOV

FROM TABULA."$(vDB)$(vHebTable)";

Else If vCaseSwitch = 3 THEN

SQL SELECT

   $(vHebPkey1),

   tabula.tabulaf.hebconvert(tabula.tabulaf.revstr("$(vDB)$(vHebTable)".$(vHebConvField1))) AS $(vHebConvField1)_HEBCOV,

   tabula.tabulaf.hebconvert(tabula.tabulaf.revstr("$(vDB)$(vHebTable)".$(vHebConvField2))) AS $(vHebConvField2)_HEBCOV

FROM TABULA."$(vDB)$(vHebTable)";

Else If vCaseSwitch = 4 THEN

SQL SELECT

   $(vHebPkey1),

   $(vHebPkey2),

   tabula.tabulaf.hebconvert(tabula.tabulaf.revstr("$(vDB)$(vHebTable)".$(vHebConvField1))) AS $(vHebConvField1)_HEBCOV,

   tabula.tabulaf.hebconvert(tabula.tabulaf.revstr("$(vDB)$(vHebTable)".$(vHebConvField2))) AS $(vHebConvField2)_HEBCOV   

FROM TABULA."$(vDB)$(vHebTable)";

END If

STORE $(vHebTable) INTO [$(vQVDPath)HebConvertTables\Heb_$(vDB)$(vHebTable).QVD];

DROP TABLE $(vHebTable);

NEXT //(for loop)

LOOP; //(while loop)

exit script

[zebhashmi]

I don't think you can use case in Qlik load

you have to use if and match for this.

[mikaelsc]

LET i = i + 1; is useless as you use next i (i would recommend that you write out your "next" completely)

and your databases loop is wrong:

should be something like:

set vDB = 'Name1','Name2'

for each vDatabase in $(vDB)

tralaala script

next vDB

[mikaelsc]

sorry, last should be next vDatabase

[balabhaskarqlik]

May be use your SQL script like this:

sql - Select inside CASE THEN - Stack Overflow