4 Replies Latest reply: Jan 30, 2014 11:45 AM by Henric Cronström RSS

    Genericc Database

      I have a generic database that I want back to a concrete database. Someting like this:

       

      // generic load with one consolidated resulting table:

      InputTable:

      LOAD * INLINE [

        object,attribute,value

        ball,color,red

        ball,diameter,10 cm

        ball,weight,100 g

        box,color,black

        box,height,16 cm

        box,length,20 cm

        box,weight,500 g

        box,width,10 cm

      ];

       

      /* resulting table (delimiter=tab):

      object color diameter weight height length width

      ball red 10 cm 100 g

      box black 500 g 16 cm 20 cm 10 cm

      */

       

      GenTable:

      Generic Load object, attribute, value Resident InputTable;

       

      ResultTable:

      LOAD Distinct object Resident InputTable;

       

      FOR i = 0 to NoOfTables()

        TableList:

        LOAD TableName($(i)) as Tablename AUTOGENERATE 1

        WHERE WildMatch(TableName($(i)), 'GenTable.*');

      NEXT i

       

      FOR i = 1 to FieldValueCount('Tablename')

        LET vTable = FieldValue('Tablename', $(i));

        LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];

        DROP TABLE [$(vTable)];

      NEXT i

       

      DROP TABLES TableList, InputTable;

       

      The problem is that our admins don't allow us to use Macros, and the FOR loops are considered macros right?

        • Re: Genericc Database
          Mario De Stefano

          Hi,

          absolutely not, FOR loops in the script are not Macros.

           

          Macros are in an external module to the script, and are written in jscript or vbscript,

           

          Hope this helps.

           

          MC

          • Re: Genericc Database
            Srikanth P

            Hi Miguel, The Macro are used to get the functionality not available with Qlikview predefined functions.

             

            For loops are just conditional execution in the scrip part and this is in built Qlikview options.

            • Re: Genericc Database
              Ralf Becher

              Hi Miguel,

               

              you already got the right and optimal solution..

               

              - Ralf

              • Re: Genericc Database
                Henric Cronström

                The For-Next loop is not a macro, just as the other answers indicate.

                 

                Further, you don't need a For-Next loop. All you need to do is to use the

                     Generic Load object, attribute, value From InputTable;

                 

                This is the optimal solution from a memory perspective. The For-Next loop will take all the created tables, and create one common table for all attributes listed in your source data, which is a table that sometimes is very sparse. The most compact solution is the one where you don't have the For-Next loop, and instead have one table per attribute.

                 

                HIC