Case SQL Load from qlikview

lior_malcka — Sun, 27 Aug 2017 21:57:44 GMT

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

Re: Case SQL Load from qlikview

zebhashmi — Fri, 31 Aug 2018 20:07:41 GMT

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

you have to use if and match for this.

Re: Case SQL Load from qlikview

mikaelsc — Sat, 01 Sep 2018 18:19:35 GMT

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

Re: Case SQL Load from qlikview

mikaelsc — Sat, 01 Sep 2018 18:20:10 GMT

sorry, last should be next vDatabase

Re: Case SQL Load from qlikview

BalaBhaskar_Qlik — Sun, 02 Sep 2018 14:33:27 GMT

May be use your SQL script like this:

sql - Select inside CASE THEN - Stack Overflow

topic Re: Case SQL Load from qlikview in QlikView

Case SQL Load from qlikview

Re: Case SQL Load from qlikview

Re: Case SQL Load from qlikview

Re: Case SQL Load from qlikview

Re: Case SQL Load from qlikview