Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I don't think you can use case in Qlik load
you have to use if and match for this.
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
sorry, last should be next vDatabase
May be use your SQL script like this: