topic Re: One column with all features (1 feature - 1 row) in QlikView

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

Sun, 10 Aug 2014 17:33:16 GMT

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

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

Sun, 10 Aug 2014 19:45:02 GMT

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 ?

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

Sun, 10 Aug 2014 20:16:20 GMT

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

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

Anonymous — Sun, 10 Aug 2014 20:32:35 GMT

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.

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

rwunderlich — Sun, 10 Aug 2014 20:46:15 GMT

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

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

MarcoWedel — Sun, 10 Aug 2014 21:48:41 GMT

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

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

MarcoWedel — Sun, 10 Aug 2014 22:06:01 GMT

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