Generic Load

[ngrunoz]

Hi guys.

I have Data which comes in in the form of a CSV file . It is structured As [ Key, Attribute, Value]. I want to pivot the Data  and have the questions as headers.

I am still very new to Qlikview so I have tried to use the Generic Load and it has created multiple Tables and I am not sure how to join the Tables to make one table  as the Attribute section has got over hundred different questions which has resulted in Hundreds of tables which looks clumsy and hard to work with.

Requirements:

1. I need to join the tables to make them One table
2. I need to only load just only a few of the questions for my analysis for argument sake lets say only ( D.O.B, Ave Income, Employment Status) over the hundreds of questions.
   1. Would you advice that I do this in the scripting
   2. or I do it in the UI
   3. Which expression can I Use
3. On Another note am I able manipulate the expressions that in the current format if it encounter a question which has a response in the form on number then it group the count expression using Class in the UI. eg  Employment Status = 500 employees then  Average Income $0 - $100 = 70 , $101 - $300 = 230 , Over $300 = 200.

• Citizen Code	Question	Response
  ZZZ1222	D.O.B	23/12/1979
  ZZZ1222	Ave Income	$560
  ZZZ1222	Employment Status	Part Time
  ZZZ1222	Education Level	High School
  ZZZ1222	Family Size	4
  ZZZ1223	D.O.B	23/12/1969
  ZZZ1223	Ave Income	$100
  ZZZ1223	Employment Status	Retired
  ZZZ1223	Education Level	Primary
  ZZZ1223	Family Size	8

[marcus_sommer]

Here is a very good explanation how to use The Generic Load which also include a join of all these tables.

But are you really sure that you will benefit from such a wide table? I suggest before doing this try to display your data with a pivot-chart in the UI.

- Marcus

[ngrunoz]

Hi Marcus. Thanks for you response. I have tried combining the tables but I am failing to make head way. I have attached a sample file. The other part of the visualization I can work fine with the data in the form of pivot tables and in the current structure before generic Load, but they is this part which I need to use some of the fields in the rows to create unique Keys. e.g one Citizen will have many transactions and would want to say CitizenCode + Date as Key. Hence the need to put them in columns and also need to extract certain text strings in the Generic Load.

[marcus_sommer]

There is no date-field within your script and also no data within your example.

- Marcus

[ngrunoz]

I can no longer see the advanced editor to attach the amended Script.

[ngrunoz]

Finally  got it. Attached here with Amended.

[ngrunoz]

Data File

[marcus_sommer · Accepted solution]

In general it worked but you have several records with the same attributes in your data and therefore you will get multiple records through the join - you might need some data-cleansing for it.

- Marcus

[ngrunoz]

Thank you very much it worked out perfectly well.

How about the second part is it possible to pick out selective attribute at first load to limit the number of tables. Ihave tried to use

WHERE Match ( Question,'Employment*','*Date*');

[marcus_sommer]

Yes, you could limit your data with a where-clause whereby in this case it should be wildmatch:

WHERE WildMatch(Question,'Employment*','*Date*');

Another clause might be working on the record-number if the structure and number of attributes is always the same - I mean something like:

where match(mod(rowno(), 11), 0,1,2,3,8);

- Marcus