topic Re: Generic Load in QlikView

Generic Load

ngrunoz — Wed, 25 Nov 2020 16:16:04 GMT

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.
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
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

Re: Generic Load

marcus_sommer — Mon, 06 Mar 2017 12:47:57 GMT

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

Re: Generic Load

ngrunoz — Mon, 06 Mar 2017 13:26:49 GMT

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.

Re: Generic Load

marcus_sommer — Mon, 06 Mar 2017 13:33:44 GMT

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

- Marcus

Re: Generic Load

ngrunoz — Mon, 06 Mar 2017 14:05:42 GMT

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

Re: Generic Load

ngrunoz — Mon, 06 Mar 2017 14:28:00 GMT

Finally got it. Attached here with Amended.

Re: Generic Load

ngrunoz — Mon, 06 Mar 2017 14:29:52 GMT

Data File

Re: Generic Load

marcus_sommer — Mon, 06 Mar 2017 16:11:03 GMT

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

Re: Generic Load

ngrunoz — Mon, 06 Mar 2017 20:22:16 GMT

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*');

Re: Generic Load

marcus_sommer — Tue, 07 Mar 2017 05:28:27 GMT

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

Re: Generic Load

ngrunoz — Mon, 13 Mar 2017 14:42:50 GMT

I have encountered another challenge. My Transactional date string is picking a time stamp and I would like to strip the time from the Date As I only want to use the Date. Should I do it after the generic Load or before and How best can I do it?

Re: Generic Load

marcus_sommer — Mon, 13 Mar 2017 14:51:27 GMT

With date(floor(YourDateField)) you could cut the time-part respectively with time(frac(YourDateField)) the date-part and I think it will be more suitable to do it after the generic-statement within your final table.

- Marcus

Re: Generic Load

ngrunoz — Mon, 13 Mar 2017 21:32:05 GMT

Thank you so much.

Re: Generic Load

ngrunoz — Thu, 20 Apr 2017 09:12:10 GMT

Hi Marcus.

Is it possible to rename columns in the Generic Load Script. for Example changing Retailer to StoreType in the example.

Re: Generic Load

marcus_sommer — Thu, 20 Apr 2017 11:56:14 GMT

I think yes but I'm not sure if it's a good idea to rename fields within the generic statement or the following join-loop. I believe it would be better to do this before or after the transforming, maybe a mapping-approach is here useful: How to Rename Fields.

- Marcus

Re: Generic Load

ngrunoz — Fri, 21 Apr 2017 06:30:28 GMT

thank you so much.