Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading a table with repeating columnnames

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

18 Replies
maximiliano_vel
Partner - Creator III
Partner - Creator III

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,

    @3

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;

Not applicable
Author

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,

    @3

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

maximiliano_vel
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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

maximiliano_vel
Partner - Creator III
Partner - Creator III

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

Anonymous
Not applicable
Author

Hi Merel,

please load repeated fields separately and try:

Table:

LOAD

FormID,

Subject,

Question1,

Question2,

etc

From ....;

Concatenate

LOAD

FormID,

Subject,

Question1,

Question2

From ....;

Not applicable
Author

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!

Not applicable
Author

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!

Anonymous
Not applicable
Author

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

Test.png