Genericc Database

[#1]

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?

[#2]

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

[#3]

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.

[rbecher]

Hi Miguel,

you already got the right and optimal solution..

- Ralf

[hic · Accepted solution]

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