

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Generic Load
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:
- I need to join the tables to make them One table
- 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.
- Would you advice that I do this in the scripting
- or I do it in the UI
- Which expression can I Use
- 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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
There is no date-field within your script and also no data within your example.
- Marcus


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I can no longer see the advanced editor to attach the amended Script.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Finally got it. Attached here with Amended.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Data File


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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*');


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- « Previous Replies
-
- 1
- 2
- Next Replies »