Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
lior_malcka
Partner - Contributor II
Partner - Contributor II

Case SQL Load from qlikview

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

4 Replies
zebhashmi
Specialist
Specialist

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

you have to use if and match for this.

mikaelsc
Specialist
Specialist

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
Specialist
Specialist

sorry, last should be next vDatabase

balabhaskarqlik

May be use your SQL script like this:

sql - Select inside CASE THEN - Stack Overflow