topic Re: Genericc Database in QlikView

Genericc DatabaseThu, 30 Jan 2014 11:05:28 GMT
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

Thu, 30 Jan 2014 11:23:26 GMT

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.

Re: Genericc Database

Thu, 30 Jan 2014 14:53:05 GMT

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

rbecher — Thu, 30 Jan 2014 15:05:25 GMT

Hi Miguel,

you already got the right and optimal solution..

- Ralf

Re: Genericc Database

hic — Thu, 30 Jan 2014 16:45:02 GMT

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