One column with all features (1 feature - 1 row)

[#1]

Hi guys,

I have simplified my case to below inline load:

LOAD * INLINE [

    Feature, Attribute, ID

    Colour, Green, 1

    Size, Big, 1

    Shape, Triangle, 1

    Colour, Green, 2

    Size, Small, 2

    Quality, Good, 2

    Material, Plastic, 2

    Colour, Red, 3

    Shape, Square, 3

    Size, Big, 4

    Material, Metal, 4

    Weight, Heavy, 4

];

Below is the result, which I would like to achieve:

Please consider that my real case has got appr. 50k IDs and 75 Features to choose from.

I have tried Generic Load, but QlikView creates 75 tables and Table Viewer is unintelligible in this scenario.

I have tried Alternate State, but could not reach satysfying result.

Left join?

Thank You for Your Help & Consideration,

Sebastian

[#2]

Sebastian , I am not sure I understand you question >

Are you saying you have a data set and you wish that you wish to load and then break this down into attributes and features? Then allow the user to select the relevant attributes and features they wish to see ?

[#3]

Hi,

G-Thanks for quick reply Robin. As in pasted image I would like to choose two features and appropriate attributes for them(ie. first feature 'Colour' - appropriate attribute 'Green' ; second feature 'Size' - adequate attribute 'Big'). As a result I would like to see on pivot table which IDs are assigned to selected features (only ID '1' in presented example meets the conditions) and also what other features are combined to them (as shown in image feature 'Shape' and attribute 'Triangle' is also assigned to ID nr '1').

Kind Regards,

Sebastian

[Anonymous · Accepted solution]

I think one way could be to create a couple of data islands like this :

Data :

LOAD * INLINE [

    Feature, Attribute, ID

    Colour, Green, 1

    Size, Big, 1

    Shape, Triangle, 1

    Colour, Green, 2

    Size, Small, 2

    Quality, Good, 2

    Material, Plastic, 2

    Colour, Red, 3

    Shape, Square, 3

    Size, Big, 4

    Material, Metal, 4

    Weight, Heavy, 4

];

One :

load

  Feature as Feature1 ,

  Attribute as Attribute1

resident Data

;

Two :

load

  Feature as Feature2 ,

  Attribute as Attribute2

resident Data

;

And then do some set analysis maybe like this:

= count ( {

<ID = P({<Feature={"$(=only([Feature1]))"} , Attribute={"$(=only([Attribute1]))"}> } ID)>

*

<ID = P({<Feature={"$(=only([Feature2]))"} , Attribute={"$(=only([Attribute2]))"}> } ID)>

  } ID )

For details on the P search this forum for "Indirect Set analysis".

See attached qvw.

Depending on what exactly you are after you may need to enhance it so that it uses a wild card if nothing is selected in one of the list boxes.

Your question was a bit unclear so I may be completely wrong, but hopefully it should give you some food for thought.

[rwunderlich]

I think Generic Load is definitely the way to go. It's easy to transform the multiple table back into a single table with a bit of script loop.

source:

LOAD * INLINE [

    Feature, Attribute, ID

    Colour, Green, 1

    Size, Big, 1

    Shape, Triangle, 1

    Colour, Green, 2

    Size, Small, 2

    Quality, Good, 2

    Material, Plastic, 2

    Colour, Red, 3

    Shape, Square, 3

    Size, Big, 4

    Material, Metal, 4

    Weight, Heavy, 4

];

Attributes:

Generic LOAD ID, Feature, Attribute

Resident source;

CombinedGenericTable:

Load distinct ID Resident source;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'Attributes.*') THEN

  LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

If you are using Qlikview Components, it's even easier:

CALL Qvc.JoinGenericTables('CombinedGenericTable', 'Attributes');

-Rob

http://masterssummit.com

http://robwunderlich.com

[MarcoWedel]

Hi Sebastian,

one possible using generic load which transforms this data model

into that

table1:
LOAD * INLINE [
    Feature, Attribute, ID
    Colour, Green, 1
    Size, Big, 1
    Shape, Triangle, 1
    Colour, Green, 2
    Size, Small, 2
    Quality, Good, 2
    Material, Plastic, 2
    Colour, Red, 3
    Shape, Square, 3
    Size, Big, 4
    Material, Metal, 4
    Weight, Heavy, 4
];

table2:
LOAD Distinct ID
Resident table1;

table3:
Generic LOAD
  ID,
  Feature,
  Attribute
Resident table1;

FOR i = 0 to NoOfTables() 
  TableList: 
  LOAD TableName($(i)) as Tablename AUTOGENERATE 1 
  WHERE WildMatch(TableName($(i)), 'table3.*'); 
NEXT i 

FOR i = 1 to FieldValueCount('Tablename') 
  LET vTable = FieldValue('Tablename', $(i)); 
  LEFT JOIN (table2) LOAD * RESIDENT $(vTable); 
  DROP TABLE $(vTable); 
NEXT i 

DROP TABLE TableList;

If the number of your features and attributes is to big to place each as a seperate listbox in your visualization then maybe a multi box could do?

hope this helps

regards

Marco

[MarcoWedel]

one description of a method to combine tables created by a generic load by Rob Wunderlich who I just saw has posted a solution on his own:

http://qlikviewnotes.blogspot.de/2010/05/use-cases-for-generic-load.html

Also one fine post regarding the combination of tables created by a generic load by Henric Cronström:

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

hope this helps

regards

Marco