Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
See attached qvw.
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