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,

Any one can help on this ?

Or is my question very confusing.

Would appreciate if some one out there provide some guiding light. 

Thanks

[#3]

Hi,

Am I posting in the wrong forum ? 

Anyone ?

[#4]

Hi,

Does anyone have any direction for the reported problem or some possible solution for above ?

[rwunderlich]

Try the attached. It gives slightly different results than what you posted above. I don't know if that's because you typoed or I'm misunderstanding the requirement.

-Rob

http://masterssummit.com

http://robwunderlich.com

[#6]

Dear Rob,

Thanks for your attached.  Unfortunately, I am using a personal edition too while our organisation small business server is coming.  So I cant open your qvw as our key are different.

Please kindly let me know which portion of my requirement is not specific so I can help to clarify in order to move this forward.

Thanks again!

Warmest Rdgs,

Chun Wey

[rwunderlich]

Here's the script from the QVW. For completeness, I've also attached the csv data file I created from your posting.

-Rob

QuestionTemp:

// CrossTable the input, using the first 7 fields as regular columns.

// Skip the first line that has the question numbers.

CrossTable(QuestionName, QuestionScore, 7)

LOAD *

FROM

QuestionData.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

// Parse the QuestionNames to get Subject & Focus.

QuestionsParsed:

LOAD

  Unique_No

  ,Evaluator_Name

  ,Evaluator_EmailAddress

  ,Subject_Dept

  ,Start_Date

  ,End_Date

  ,[Qns 1]

  ,TextBetween(QuestionName,' ', '-') as Focus

  ,SubField(QuestionName,'-',2) as Subject

  ,QuestionScore

  // Generate an index that will be used to join with Question#

  ,AutoNumber(recno(),Unique_No) as QuestionIndex

RESIDENT QuestionTemp;

DROP TABLE QuestionTemp;

//Questions:

LEFT JOIN(QuestionsParsed)

LOAD

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

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

FROM

QuestionData.csv

(fix, codepage is 1252)

where RecNo()=1;

[#8]

Thanks Rob for the effort in doing this.  Will try it out and update here.

Appreciate it.

[#9]

Hi Rob,

Update:

I tried out your script, it works as what I want.  The focus areas are been extracted and align correct against the data and subject name. 

For the second portion as what I had mention in mine first post, I also need to align the Question number ( the very first row) to align correctly with the data and subject name. 

As such, i follow your concept that you given in the script.  However, I cant seem to derive the answer out. 

Below is the information:

Screenshot

The above pic show the correct alignment of Evaluator, Focus, Subject & Data.  However, the Question column is not align correctly.

The above pic is the 'parsedQuestion2' table which I tried to follow your concept.  The alignment is wrong.  The Questions2 column should be in this order: Q1,Q2,Q3,Q4,Q1,Q2,Q3,Q4.

Code:

QuestionTemp2: // This is to get the questions

CrossTable(GROUP2, Data2, 5)

LOAD *

FROM

[Peer Appraisal.xlsx]

(ooxml, embedded labels, table is MSF__Resident_Name_A);

QuestionsParsed2:

LOAD

// GS_MCR_No,

    left(GROUP2,1) & TextBetween(GROUP2,'#','_')  as Questions2

   // Generate an index that will be used to join with Question#

// ,AutoNumber(recno(),GS_MCR_No) as QuestionIndex

  ,AutoNumber(recno()) as QuestionIndex

RESIDENT QuestionTemp2;

DROP TABLE QuestionTemp2;

QuestionTemp:

CrossTable(GROUP, Data, 5)

LOAD *

FROM

[Peer Appraisal.xlsx]

(ooxml, embedded labels, header is 1 lines, table is MSF__Resident_Name_A);

// Parse the QuestionNames to get Subject & Focus.

QuestionsParsed:

LOAD

// GS_MCR_No,

     GS_Peer_Evaluator_Name,

//    GS_Peer_Evaluator_EmailAddress,

//    GS_Peer_Subject_Rotation,

//    GS_Peer_Start_Date,

//    GS_Peer_End_Date,

  TextBetween(GROUP,' ', '-') as Focus

  ,SubField(GROUP,'-',2) as Subject

  ,Data

  // Generate an index that will be used to join with Question#

// ,AutoNumber(recno(),GS_MCR_No) as QuestionIndex

  ,AutoNumber(recno()) as QuestionIndex

RESIDENT QuestionTemp;

DROP TABLE QuestionTemp;

//Questions:

LEFT JOIN(QuestionsParsed)

LOAD

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

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

FROM

[Peer Appraisal.xlsx]

(fix, codepage is 1252)

where RecNo()=1;

LEFT JOIN(QuestionsParsed2)

LOAD

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

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

FROM

[Peer Appraisal.xlsx]

(fix, codepage is 1252)

where RecNo()=1;

P.S: I took out GS_MCR_No as the linkage is not correct.  Will be doing a left join to another table that has MCR_No using subject name as key.

[#10]

Hi Rob,

Any help on this ?

Thanks