topic Re: Using a pivot table for large amounts of data in QlikView

Using a pivot table for large amounts of data

Thu, 11 Jul 2013 20:32:29 GMT

Does anyone know how i can solve the following problem;

I have a star schema in qlikview and all the data is connected by an item_id as a key from all the tables to my product data table. One of the table is called item_attribute_value which has item to attribute and attribute to value relationships and the data from that table is about 300 million rows. I load the same data from the mentioned table 5 times and call the tables item_attribute_value_1 to item_attribute_value_5.

I want to pivot the data like the following;

Item -> Attribute_1->Value_1->Attribute_2->Value_2->Attribute_3->Value_3->Attribute_4->Value_4->Attribute_5->Value_5

when i try to do this using the Item,Attribute_1,Attribute_2,Attribute_3,Attribute_4,Attribute_5 as my dimensions

and maxString(Value_1),maxString(Value_2),maxString(Value_3),maxString(Value_4),maxString(Value_5) as my expressions then the pivot table never loads.

I have tried using list boxes for each attribute_1 to attribute 5 and Value_1 to Value_5 but that results in a vertical relationship and i would like the horizontal

relationship based off the item like how a pivot table gives me when i just to Item->Attribute_1->maxString(Value_1).

Also tried using a table box but it also can not handle that much data and results in a cross across the table object.

I would really appreciate any help i can get on this issue.

Thanks

Re: Using a pivot table for large amounts of data

Tony_Strano — Thu, 11 Jul 2013 21:11:57 GMT

You are trying to pivot 1.5 billion records across all the dimensions since the Item key is your only key joining all the tables. That doesn't sound like a Star schema. The only join for a key like the item key is between the product dimension and the fact table. The rest of the dimensions should use their own surggate keys to their foreign keys in the fact table. You're basically associating all fields against all fields 5 times. I can't see how that would work or be worth analyzing the results.

Re: Using a pivot table for large amounts of data

Thu, 11 Jul 2013 22:06:28 GMT

Thanks for the response. It is very helpful. This is the scenario of my problem;

An item can have many attributes and we are loading a maximum of 5 attributes per item, each attribute can have many values and we are loading a maximum of 5 values per item.

Say my item is a ruler and a ruler can be described with a color,length,width and so on and a color can be green,red and so on and length can have many values to, same thing with width.

I want users to be able to pick an item which is a ruler in this case and then pick color which will be in attribute_1, pick a red under value_1 and can also pick length under attribute_2 and 10 inches under value_2 and so on.

That is really what i am trying to do. all the rest of the data in my application is connected through an item.

Per your suggestion i have decided to only join item_attr_value_1 to product data and join the rest of the item attribute values tables to item_attr_value_1 instead of joining them in the product data table like i was doing.