topic Re: Best way to process one field with multiple values (comma separated) in QlikView

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

Fri, 15 May 2015 09:59:25 GMT

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

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

Gysbert_Wassenaar — Mon, 18 May 2015 17:31:26 GMT

See attached qvw.

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

Fri, 12 Jun 2015 12:56:26 GMT

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