0 Replies Latest reply: Aug 27, 2017 5:57 PM by lior malcka RSS

    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