Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Hopefully soemone can help me with this puzzle.
I want to load a questionnaire and I have the following input file.
Country | Company | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 |
---|---|---|---|---|---|---|---|
be | abc | 1 | 2 | 3 | 4 | 4 | 4 |
fr | abc | 2 | 3 | 4 | 5 | 2 | 2 |
gb | cde | 7 | 6 | 5 | 6 | 7 | 7 |
us | cde | 5 | 7 | 8 | 9 | 0 | 8 |
Also I have a referential file
Country
Question Code | Description |
---|---|
Q1 | How are you |
Q2 | What is your rating of...? |
Q3 | What's your age? |
Now I would like to create the following structure
UKEY | Question Code | Answer |
---|---|---|
1 | Q1 | 1 |
1 | Q2 | 2 |
1 | Q3 | 3 |
Where I can link the UKEY to a table with company/country info.
How can I do this easily. Note the number of questions is like a 1000.
For some reason the transpose function doesn't work as I want it to work.
Best regards,
Arjan
Hi Arjan,
I´ve Created a source file with your data (Quest.xls) and used it to obtain the desired table. You can do it with CrossTable (it has an assistant to do it. It´s a Transformation Step)
Please se below the script:
[Referential File]:
LOAD [Question Code],
Description
FROM
Quest.xlsx
(ooxml, embedded labels, table is [Referential file]);
Quest:
CrossTable([Question Code], Answer, 2)
LOAD Country,
Company,
Q1,
Q2,
Q3,
Q4,
Q5,
Q6
FROM
Quest.xlsx
(ooxml, embedded labels, table is Quest);
-- If you have Endless questions, you can use * instead of field names. Hope this helps.
Regards
L.