Best way to process one field with multiple values (comma separated)

[#1]

Hi all,

I need to load and investigate some CSV file with less transformation as possible due to important data volume.

I have this type of data (dumb data, just for example ) :

ID    |      Name   |     Responsibility                   |    Hobbies

1      |   John  |         Manager                             |        Tennis

2      |   Tim      |       Manager, Director               |        Tennis, windsurf

3      |   Joe    |         Manager, IT, Employee       |        Tennis, windsurf, football

I need to create people repartition by Responsibility based "Responsibility" field and comma separated information.

From the data previously presented, I would extract :

   3 people acting as "Manager" (i.e 3/5th of cases)

   1 people acting as "Director" (i.e 1/5th of cases)

   1 people acting as "Employee" (i.e 1/5th of cases)

But also :

   3 people associated with "Tennis" (5/5th=100%)

   2 people associated with "Windsurf" (2/5th)

   1 people associated with "Football" (1/5th)

And more (drill down on "Manager" population) :

   From all "Manager" (3 people associated to "Manager"), 3 are associated with "tennis" (100% "Manager"),  2 "Windsurf (2/3rd) and 1 football (1/3rd)

What could be the best way to work with these fields with multiple data separated with "comma" (or whatever else).

I would like to represent the result in a pie graphic with a first dimension base on "Responsibility", a second dimension based on Hobbies and the Expression would be the number of people linked with dimensions.

I have in mind to use subfield function in the load sequence to split "Responsability" and "Hobbies" fields, creating some new fields. What about performance and how to bult a pie graph with one dimension based on several fields ?

Thanks in advance for your help.

Gilles

Ce message a été modifié par : Gilles

[Gysbert_Wassenaar]

See attached qvw.

[#3 · Accepted solution]

Hi,

Sorry for the delay to answer.

Many thanks Gysbert for your answer. Unfortunately, I can't read the qvw because I'm using the personal Desktop QlikView version.

Anyway, I found a solution for my question. I used the Subfield function wich will create an entry line for each subfield found in a string.

If i take my example, the orignal table is (in a CSV for example) :

ID    |      Name   |     Responsibility                   |    Hobbies

1      |   John  |         Manager                             |        Tennis

2      |   Tim      |       Manager, Director               |        Tennis, windsurf

3      |   Joe    |         Manager, IT, Employee       |        Tennis, windsurf, football

I will load this table with :

REsponsibilityTable:

Load

    ID,

    Name,

    SubField(Responsibility, ',' ) as Responsibility

FROM

input.csv

I will have in QlikView memory :

ID    |      Name   |     Responsibility

1      |   John  |         Manager

2      |   Tim      |       Manager

2      |   Tim      |       Director

3      |   Joe    |         Manager

3      |   Joe    |         IT

3      |   Joe    |         Employee

That's it.

Gill