topic Re: Re: Capture all data in an excel without changing Source File in QlikView

Capture all data in an excel without changing Source File

Fri, 30 Aug 2013 06:09:44 GMT

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.

Re: Capture all data in an excel without changing Source File

Mon, 02 Sep 2013 04:08:12 GMT

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

Re: Capture all data in an excel without changing Source File

Wed, 04 Sep 2013 03:03:36 GMT

Hi,

Am I posting in the wrong forum ?

Anyone ?

Re: Capture all data in an excel without changing Source File

Fri, 06 Sep 2013 02:45:24 GMT

Hi,

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

Re: Re: Capture all data in an excel without changing Source File

rwunderlich — Fri, 06 Sep 2013 04:56:25 GMT

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

Re: Capture all data in an excel without changing Source File

Fri, 06 Sep 2013 06:39:18 GMT

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

Re: Re: Capture all data in an excel without changing Source File

rwunderlich — Fri, 06 Sep 2013 14:54:28 GMT

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;

Re: Re: Capture all data in an excel without changing Source File

Mon, 09 Sep 2013 03:17:17 GMT

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

Appreciate it.

Re: Capture all data in an excel without changing Source File

Thu, 12 Sep 2013 06:58:46 GMT

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.

Re: Capture all data in an excel without changing Source File

Wed, 18 Sep 2013 07:27:48 GMT

Hi Rob,

Any help on this ?

Thanks

Re: Capture all data in an excel without changing Source File

Mon, 23 Sep 2013 01:41:58 GMT

Hi Rob,

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

thanks!

Re: Capture all data in an excel without changing Source File

rwunderlich — Mon, 23 Sep 2013 14:09:32 GMT

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;

Re: Capture all data in an excel without changing Source File

Thu, 02 Oct 2014 02:00:22 GMT

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

Re: Capture all data in an excel without changing Source File

rwunderlich — Fri, 24 Oct 2014 07:36:54 GMT

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

-Rob