Capture all data in an excel without changing Source File

[#1]

V1	V2	V3	V4	Q1	Q2#1_1	Q2#1_2	Q2#1_3	Q2#1_4	Q2#1_5	Q2#1_6	Q2#1_7	Q2#1_8	Q2#2_1	Q2#2_2	Q2#2_3	Q2#2_4	Q2#2_5	Q2#2_6	Q2#2_7	Q2#2_8	Q2#3_1	Q2#3_2	Q2#3_3	Q2#3_4	Q2#3_5	Q2#3_6	Q2#3_7	Q2#3_8	Q2#4_1	Q2#4_2	Q2#4_3	Q2#4_4	Q2#4_5	Q2#4_6	Q2#4_7	Q2#4_8
Unique_No	Evaluator_Name	Evaluator_EmailAddress	Subject_Dept	Start_Date	End_Date	Qns 1	: Responsibility-Test A	: Responsibility-Test B	: Responsibility-Test C	: Responsibility-Test D	: Responsibility-Test E	: Responsibility-Test F	: Responsibility-Test G	: Responsibility-Test H	: Hardworking-Test A	: Hardworking-Test B	: Hardworking-Test C	: Hardworking-Test D	: Hardworking-Test E	: Hardworking-Test F	: Hardworking-Test G	: Hardworking-Test H	: Teamplayer-Test A	: Teamplayer-Test B	: Teamplayer-Test C	: Teamplayer-Test D	: Teamplayer-Test E	: Teamplayer-Test F	: Teamplayer-Test G	: Teamplayer-Test H	: Integrity-Test A	: Integrity-Test B	: Integrity-Test C	: Integrity-Test D	: Integrity-Test E	: Integrity-Test F	: Integrity-Test G	: Integrity-Test H
1234567A	Evaluator A	EvaluatorA@evaluator.com	English	23/08/2013 18:14	23/08/2013 18:29	1	6	6	6	6	3	5	3	5	6	4	6	6	4	4	3	5	6	6	6	6	4	4	3	5	6	6	6	6	4	4	3	5
24680123B	Evaluator B	EvaluatorB@evaluator.com	Maths	23/08/2013 18:14	23/08/2013 18:29	2	3	4	5	5	2	3	5	3	5	6	4	6	6	4	4	3	5	6	6	6	6	4	4	3	5	6	6	6	6	4	4	3

Hi All,

I am trying to upload the above data from Excel into Qlikview.  This excel data is generated from Source and I do not want to meddle with the data here.

So this is the problem:

Below is the focus of 4 areas,namely Responsibility, Hardworking, Teamplayer, Integrity.  Below each of this qns, is the subject name which is tie to the Focus area.  E.g.  Responsibility-Test A where Test A is the name of the subject.

The number of test subject varies.  It can range from a few to 30 plus.  How can I capture each Subject Name with the question number and Focus Area ??

Q2#1_3 to Q2#1_8:  Responsibility

Q2#2_1 to Q2#2_8: Hardworking

Q2#3_1 to Q2#1_8: Teamplayer

Q2#4_3 to Q2#1_8: Integrity

Do appreciate if there is some guidance on this, though i already explore Cross table but I cant get it work as there is more than 1 subject name.  Was thinking of doing a loop kinda stuff to churn this out.

Thanks in advance.                                                                                                                                                             

[#2]

Hi Rob,

Do let me know when you are back as I need to get this working

thanks!

[rwunderlich · Accepted solution]

HI,

I did the example using a csv, you have an xlsx. The last part, the LEFT JOIN, has to be done with a file that can be read as fixed. So for you, create a copy of the xlsx file as csv for that portion.

HeaderTemp:

LOAD *

FROM

[Peer Appraisal.xlsx]

(ooxml, Resident_Name_A);

STORE HeaderTemp INTO HeaderTemp.csv (txt);

//Questions:

LEFT JOIN(QuestionsParsed)

LOAD

  subfield(@1:n,',') as Question,

  rowno()-5 as QuestionIndex // This index will match the correct column

FROM

[HeaderTemp.csv]

(fix, codepage is 1252)

where RecNo()=1;

[#4]

Hi Rob,

Had resolved this as per your kind guidance (1 year ago though ).

Just want to thank you to have a closure on this topic.

Thanks!

Chun Wey

[rwunderlich]

You're welcome. Please mark something as correct to close the thread.

-Rob