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

[pgrenier]

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

[#3]

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

[#4]

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

[Anonymous]

Hi Merel,

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

[maximiliano_vel]

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

[#7]

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

[Anonymous]

try to implement Philippe's solution

it should work.

or modify it according to your requirement.

[#9]

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

[#10]

Thanks for your help, Neetha!