topic Re: Loading a table with repeating columnnames in QlikView

Loading a table with repeating columnnames

Fri, 31 Jul 2015 19:53:13 GMT

Hi there,

I'm fairly new to QlikView and I'm encountering a problem. Apologies if I'm not posting this in the right group.

I'm trying to load a table that looks roughly like this:

FormID Subject Question1 Question2 Subject Question1 Question2 etc

1 Something1 Answer Answer Something3

2 Something2 Answer Answer Something3

3 Something3 Answer Answer Something1

4 Something1 Answer Answer Something2

5 Something3 Answer Answer Something1

I want the table to load like:

FormID Subject Question1 Question2

1 Something1 Answer Answer

2 Something2 Answer Answer

3 Something3 Answer Answer

4 Something1 Answer Answer

5 Something3 Answer Answer

1 Something3 etc

2 Something3

3 Something1

4 Something2

5 Something1

So all subjects will be in 1 column.

Does anybody have any suggestions?

Extra difficulty is that the file from which these rows and fields are loaded will change everytime, so the amount of subject fields and question fields wil change with every reload.

I hope someone can help!

Cheers,

Merel

Re: Loading a table with repeating columnnames

maximiliano_vel — Sat, 01 Aug 2015 04:41:27 GMT

Assuming that you can do without the FormID, and the changes always the same

Subject; Question1; Question2; Subjet; Question1; Question2

The growth in colums are always the same 1 Subject Column and 2 Questions Columns

Attach the source files, change the script accordingly...

SurveyTmp:

First(1)

LOAD *

FROM

(txt, codepage is 1252, no labels, delimiter is ';', msq);

LET vColsGroup = 3;

LET vNoOfCols = NoOfFields('SurveyTmp') - $(vColsGroup);

LET vLoop = 1;

LET vUnwrap = '';

DROP Table SurveyTmp;

Do While vLoop = 1

LET vUnwrap = '$(vUnwrap)' & ',Unwrap(Col, Pos(Top, $(vNoOfCols)))';

LET vNoOfCols = $(vNoOfCols) - $(vColsGroup);

if(vNoOfCols < vColsGroup) then

LET vLoop = 0;

ENDIF

Loop

SurveyTmp:

LOAD @1,

@2,

FROM

(txt, codepage is 1252, no labels, delimiter is ';', msq, header is 1 lines, filters(

Remove(Col, Pos(Top, 1))

$(vUnwrap)

));

NOCONCATENATE

Survey:

LOAD

RowNo() as FormID,

@1 as Subject,

@2 as Question1,

@3 as Question2

RESIDENT SurveyTmp

WHERE Len(@1)<>0;

DROP Table SurveyTmp;

Re: Loading a table with repeating columnnames

Sun, 02 Aug 2015 18:17:05 GMT

Hi Maximiliano,

Thanks a lot for your anwer!

I've been trying to get it to work but I'm not succeeding.

One of the problems is this part of the code:

SurveyTmp:

LOAD @1,

@2,

FROM

(txt, codepage is 1252, no labels, delimiter is ';', msq, header is 1 lines, filters(

Remove(Col, Pos(Top, 1))

$(vUnwrap)

));

Qlikview underlines the part with $(vUnwrap) as invalid. If I do a reload without this variable, I loose the data from my file.

Also, in my example I simplified the source files by saying there were 2 questions per subject, but actually there are 16 questions per subject in this file, and the last 5 columns are related to the FormID rather than to a subject.

So more like this:

FormID Subject Question1 Question2,3,4etc Subject Question1 Question2,3,4 Age Sex (etc)

1 Something1 Answer Answer Something3 34 m

2 Something2 Answer Answer Something3 etc f

3 Something3 Answer Answer Something1

4 Something1 Answer Answer Something2

5 Something3 Answer Answer Something1

As an added bonus, with each reload the source file changes. There will always be a FormID and the last 5 columns will always be the same, but the amount of subjects and the amount of answers per subject will vary.

And unfortunately I do need the FormID, as it will link with other files...

I understand that it's a tough one, and I hope you or anyone else might have a solution! In any case, many thanks for your help so far.

Cheers,

Merel

Re: Loading a table with repeating columnnames

maximiliano_vel — Mon, 03 Aug 2015 16:00:43 GMT

Just to check, here is the qvw, extract all in the same folder, Yes QlikView mark $(vUnwrap) as invalid, but it works.

Re: Loading a table with repeating columnnames

Tue, 04 Aug 2015 17:56:32 GMT

Hi Maximiliano,

Thanks again!

Yes, your files work fine. However, if I use your script and only change the source file, I get no errors and I do get lists with the proper titels (ID, Subject, Question 1 and Question 2), but no values...

Any idea how that's possible? Sorry if it's a noob question..

Cheers,

Merel

Re: Loading a table with repeating columnnames

maximiliano_vel — Tue, 04 Aug 2015 18:05:38 GMT

Perhaps if i could see what it is are you describing....???

Re: Loading a table with repeating columnnames

Anonymous — Tue, 04 Aug 2015 18:23:13 GMT

Hi Merel,

please load repeated fields separately and try:

Table:

LOAD

FormID,

Subject,

Question1,

Question2,

etc

From ....;

Concatenate

LOAD

FormID,

Subject,

Question1,

Question2

From ....;

Re: Loading a table with repeating columnnames

Tue, 04 Aug 2015 19:21:18 GMT

Hi Maximiliano,

Attached is the file that I'm trying to load.

The S1 etc fields are the 'Question' fields that are related to the Productcodes.

The amount of Productcode fields will change every time as will the amount of related S fields...

So basically each FormID answers questions about several products. The products are in random order for each FormID. So for example, FormID 1 answers questions about Productcodes 231- 234 - 125 in that order, but FormID 2 answers questions about the same Productcodes but in say order 234 - 125 - 231. To make adequate calculations, I have to be able to gather all Productcodes from the different FormIDs. But I also want to be able to say how people in certain age ranges rate a product, so I have to know which answers belong to which FormID etc.

I hope this clarifies what I mean, and again, many thanks!

Re: Loading a table with repeating columnnames

Tue, 04 Aug 2015 19:22:57 GMT

Hi Neetha,

Thanks for your answer!

I've been thinking about that, but the problem is that everytime the amount of fields changes as each file will belong to a different type of questionnaire. See also above my answer to Maximiliano.

Any other ideas? Thanks in any case!

Re: Loading a table with repeating columnnames

Anonymous — Tue, 04 Aug 2015 19:45:35 GMT

so the above solution will not work for you,i assumed your requirement is below output:

Re: Loading a table with repeating columnnames

pgrenier — Tue, 04 Aug 2015 19:53:14 GMT

Hello Merel,

Hope this helps:

RawCsvFile:

LOAD *

// Formuliernummer

// , Productcode_1, Uiterlijk_1, Totaal_oordeel_1, S12_1, S11_1, S10_1, S9_1, S8_1, S7_1, S6_1, S5_1, S4_1, S3_1, S2_1, S1_1

// , Productcode_2, Uiterlijk_2, Totaal_oordeel_2, S12_2, S11_2, S10_2, S9_2, S8_2, S7_2, S6_2, S5_2, S4_2, S3_2, S2_2, S1_2

// , Productcode_3, Uiterlijk_3, Totaal_oordeel_3, S12_3, S11_3, S10_3, S9_3, S8_3, S7_3, S6_3, S5_3, S4_3, S3_3, S2_3, S1_3

// , Productcode_4, Uiterlijk_4, Totaal_oordeel_4, S12_4, S11_4, S10_4, S9_4, S8_4, S7_4, S6_4, S5_4, S4_4, S3_4, S2_4, S1_4

// , Productcode_5, Uiterlijk_5, Totaal_oordeel_5, S12_5, S11_5, S10_5, S9_5, S8_5, S7_5, S6_5, S5_5, S4_5, S3_5, S2_5, S1_5

// , Productcode_6, Uiterlijk_6, Totaal_oordeel_6, S12_6, S11_6, S10_6, S9_6, S8_6, S7_6, S6_6, S5_6, S4_6, S3_6, S2_6, S1_6

// , merken_weleens

// , merken_2

// , Freq_2

// , Leeftijd_2

// , Geslacht_2

From Test.CSV (txt, utf8, embedded labels, delimiter is ',');

/* There are 6 fields that are to be assigned to each product: Formuliernummer, merken_weleens, merken_2, Freq_2, Leeftijd_2, Geslacht_2

And each product has a total of 15 fields each */

LET vNbProducts = (NoOfFields('RawCsvFile') - 6)/15;

FOR i=1 to $(vNbProducts)

Data:

LOAD Formuliernummer

, $(i) as ProductGroup

, Productcode_$(i) as ProductCode

, Uiterlijk_$(i) as Uiterlijk

, Totaal_oordeel_$(i) as Totaal_oordeel

, S12_$(i) as S12, S11_$(i) as S11, S10_$(i) as S10, S9_$(i) as S9, S8_$(i) as S8, S7_$(i) as S7

, S6_$(i) as S6, S5_$(i) as S5, S4_$(i) as S4, S3_$(i) as S3, S2_$(i) as S2, S1_$(i) as S1

, merken_weleens, merken_2, Freq_2, Leeftijd_2, Geslacht_2

Resident RawCsvFile;

NEXT i;

DROP Table RawCsvFile;

Cheers,

Philippe

Re: Loading a table with repeating columnnames

Tue, 04 Aug 2015 20:29:00 GMT

Hi Philippe,

Thanks!! This looks really promising!

Only 2 more questions:

What if in the source file the fields S1 etc are usually labelled a bit more elaborate, say S1_bitter_1, S2_moist_1 etc. Is there a way to have QlikView look only at the first and last parts of the fieldname (S1 and _1)?
The first question is related to the second: the source files will differ with every reload, in that there might be less Productcodes and/or less 'S1'-type fields. Say Productcode_1 thru 5 w/ questions S1 thru S10. Is there a way to further standardize the formula so uploading the next file would go just as well?

Thanks again!

Cheers,

Merel

Re: Loading a table with repeating columnnames

Tue, 04 Aug 2015 20:33:49 GMT

Hi Neetha,

Yes, that is the output that I want!

The only problem is that the files will change everytime, the source file I mean. For this file (data from a questionnaire) there are 6 Productcode fields with 15 related fields (2 other and 13 fields starting with an S). But the next file might contain 3 Productcode fields with 18 related fields, or 8 with 10 related fields, and so on. I'm hoping to find a way to write a script that works for all those kinds of files.

I hope this clarifies my problem!

Cheers,

Merel

Re: Loading a table with repeating columnnames

Anonymous — Tue, 04 Aug 2015 20:33:53 GMT

Hi Merel,

what are the fields changing across files and which fields state static across files.

Re: Loading a table with repeating columnnames

maximiliano_vel — Tue, 04 Aug 2015 20:36:48 GMT

So... loaded in QlikView should be something like attached file??? without the red rows!

Re: Loading a table with repeating columnnames

Tue, 04 Aug 2015 20:45:32 GMT

Hi Neetha,

The fields that are changing are:

The amount of Productcode fields;
The amount of Uiterlijk fields (related to the Productcode fields);
The amount of Totaal_oordeel fields (related to the Productcode fields);
The amount of fields starting with S# related to the productcode fields AND the name of these fields (for example, S1 might be 'S1_bitter_1' in one file, but 'S1_warm_1' in the next)

The fields that stay the same are:

Formuliernummer
The last 5 fields ('merken_weleens' thru 'Geslacht_2')

Cheers,

Merel

Re: Loading a table with repeating columnnames

Anonymous — Tue, 04 Aug 2015 20:49:52 GMT

try to implement Philippe's solution

it should work.

or modify it according to your requirement.

Re: Loading a table with repeating columnnames

Tue, 04 Aug 2015 20:50:03 GMT

Well, yes, if you view Booking Week as Formuliernummer, year as Productcode and Value as the 'S' fields. Only thing I would miss then are the 5 fields at the end (a.o. Leeftijd and Geslacht)..

Re: Loading a table with repeating columnnames

Tue, 04 Aug 2015 20:50:32 GMT

Thanks for your help, Neetha!