Loading a table with repeating columnnames

[#1]

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

[maximiliano_vel]

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;

[#3]

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]

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

[#5]

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]

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

[Anonymous]

Hi Merel,

please load repeated fields separately and try:

Table:

LOAD

FormID,

Subject,

Question1,

Question2,

etc

From ....;

Concatenate

LOAD

FormID,

Subject,

Question1,

Question2

From ....;

[#8]

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!

[#9]

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]

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