4 Replies Latest reply: Jan 1, 2012 10:26 AM by davideps RSS

    Please help with a variation of the generic table idea

      Hello,  I am new to Qlikview and able to load my data in its current state. However, I would like to distinguish between "objects" and "attributes" which are currently stored in the same ITEMS table. Objects start with a pound sign (#) and attributes start with a period (.). I have a GROUP table that combines ITEMS to make complete products. There will be only one Object per group and at least one Attribute. Here are example tables for a chair (red, 4 legs), a car (blue), and another chair (brown), which are stored on disk as XML files:  ORIGINAL ITEMS TABLE item_id     name 1             #Chair 2             #Car 3             .legs=4 4             .color=red 5             .color=blue 6            .color=brown  ORIGINAL GROUP TABLE group_id     group     o_id 1                    1           1 2                    1           3 3                    1           4 4                    2           2 5                    2           5 6                    3           1 7                    3           6  I would like to end up with listboxes for OBJECT, GROUP, and for each attribute. I believe this is identical to the Generic Database example on page 375 of the Version 11 reference manual, except I am dealing with a case where multiple objects of the same type exist (ball1,box1,ball2, box2, etc) which is handled by the GROUP column (acts like an ID for an Object/Attributes set).   Solution as List Boxes (not rows of a table)  OBJECTS    GROUP      .legs     .color #Chair                1              4          red #Car                   2                          blue                            3                          brown                                  I understand how to identify Objects and Attributes using left(string,1) and how to separate attributes from values using index(), left(), and right(). But, I'm unsure how to break up the ITEMS table and generate separate new tables while maintaining the connections shown in the GROUP table or how to keep the connection in .color=red after I've split them into attribute and value. Any suggestion/advice would be welcome!   thank you, -david

        • Re: Please help with a variation of the generic table idea

          Sadly, I see that my example tables look unreadable in the post. And, the insert table button in the advanced editor does not appear to work in my browser (IE 9). Hopefully readers can still get the gist of what I want to accomplish.   -david

          • Re: Please help with a variation of the generic table idea
            Muzammil Syed

            Dear David,

            I was not able to exactly as you said but i think even this would help you

             

             

             

            Hope that is helpful.

              • Re: Please help with a variation of the generic table idea

                Syed,  I hope you received my thank you email for the code you sent. It was a very helpful first step and also the first time I manipulated tables after loading (I'm a beginner). Your code creates the exact situation in the center picture of page 375 in the QV11 reference manual. I think what I need to do is add the "group" number to the attribute table in your code and then reload the attribute table as GENERIC. That will move me from the first picture on page 375 to the last picture--skipping the inbetween state I'm in now. I want to let Qlikview do the heavy lifting of creating a table for each attribute and filling it with the correct values. But, I'm having some difficulty with adding the group number. I'll work on it more tomorrow. I'm open to suggestions of course.  -david  PS: Happy new year to all!

                  • Re: Please help with a variation of the generic table idea

                    I think I've solved this (in a rather ugly way). My real data is a bit more complex than the example I posted but Syed's suggestions got me rather far. The final step on the real data was a massive join:  test: LOAD attr_id, attr_name, attr_val RESIDENT attribute; JOIN LOAD obj_id, obj_name RESIDENT object; JOIN LOAD q_id, obj_id, attr_id RESIDENT group; JOIN LOAD q_id RESIDENT q;  That produced all the correct rows and then a ton of rows without a q_id or without an object_name. So, the final step was to filter these out while loading as GENERIC.  final: GENERIC LOAD q_id, obj_name, attr_name, attr_val  RESIDENT test  WHERE NOT(ISNULL(q_id)) AND NOT(ISNULL(obj_name));     DROP TABLES [nearly all other tables]   The success of this process raises other questions that I will post with an appropriate topic name.  thanks again, -david