Thursday, August 7, 2014

QV7 Generic load in qlikview

A generic table is commonly used to store attribute values for different objects.
eg. ball and box is the example

These attributes are not necessarily shared across all objects. Objects might have common field or not.
Let have we have two objects ball and box
ball attribute - color , weigth , diameter
box attribute - color , weigth , length, heigth, width

Here we can we color and weigth are common field between two objects. And the attributes and its values store in a single table as below.




Enter the Generic prefix.
It converts the data to a structure where each attribute is placed in a field of its own (each attribute now act as a an object entity). Another way to express it is to say that it takes field values and converts these to field names. If you compare it to the Crosstable prefix, you will find that they in principle are each other’s inverses.


The syntax is
   Generic Load Key, Attribute, Value From … ;


There are however a couple of things worth noting:
    Usually the input data has three columns: one qualifier field (Key in the above example), an Attribute and a Value. But you may also have several qualifying fields. If you have four or more columns, all columns except the two last will be treated as qualifying fields.
    The Generic prefix will create several tables; one table per attribute. This is normally not a problem. Rather, it is an advantage: It is the least memory-consuming way to store data if you have many attributes.


Now open qlikview and create new document save it. and open script editor
// 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
];
 

GenTable:
Generic Load object, attribute, value Resident InputTable;
 



 
after reload check data model


Now as you can see in image, every attribute act as its own object entity.

after search alot on google i have found that the use of generic load is very rare. It can be use only when we want to change the orientation of the table.





Paste below code in same script editor to made orientation in table.
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

source -
http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

4 comments:

  1. very useful info you shared on QV7 Generic load in qlikview, as if is very helpful for us and gained more knowledge from it. We are very greatful and thanks a lot. QlikView Training in Hyderabad

    ReplyDelete
  2. Thanks for taking the time to discuss about qlikview, I feel strongly about it and love learning more on this topic.
    Visit Us:
    Microsoft Dynamics CRM Training|
    Qlikview Training

    ReplyDelete
  3. MaxMunus Offer World Class Virtual Instructor led training on QLIKVIEW. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 1000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.For Demo Contact us.
    Akash Kumar Asthana
    MaxMunus
    E-mail: akash@maxmunus.com
    Skype id: akashkumarasthana
    Ph:(0) 9035888988 / 080 - 41103383
    http://www.maxmunus.com/

    ReplyDelete
  4. Thx For Your Tutorials and Information On Qlikview Online Training it will be most usefull for Beginers, Who are learning Qlikview Through Online Training and Web

    ReplyDelete

web stats