topic Re: Searching for solution. in QlikView

Searching for solution.

userid128223 — Wed, 03 Oct 2018 15:16:46 GMT

I have worksheet with few different sheets in them.

Sheet: Main

Student	Subject	Student#
John		123
Mike		223
Trout		455
Nancy		654
Karl	English	555

Sheet: Science

StudentNumber	Grade
123	55
223	78
455	67
654	59

Sheet: Math

Number	Grade
123	88
223	92
455	83
654	77

As you can see header is not unique for Student number. Secondly I wanted to Merge everything in 1 table where when reading Science Sheet & Math sheet will be written to table. Below is what i want as final results.

Header 1	Student#	Subject	Grade
John	123	Science	55
Mike	223	Science	78
Trout	455	Science	67
Nancy	654	Science	59
John	123	Math	88
Mike	223	Math	92
Trout	455	Math	83
Nancy	654	Math	77

Re: Searching for solution.

YoussefBelloum — Wed, 03 Oct 2018 15:30:52 GMT

Hi,

what do you mean header is not unique for Student Number ?

when loading Science table: rename StudentNumber as Student# and create a flag 'Science' as Subject

when loading Math table: rename StudentNumber as Student# and create a flag 'Math' as Subject

load Main table without changing anything.

this way your 3 tables will be connected by the key StudentNumber and you will have a new field Called Subject that you can use on your table the way expected above

Re: Searching for solution.

userid128223 — Wed, 03 Oct 2018 15:48:17 GMT

I tried below and it did not work. Let me know what i am doing wrong.

Main:

LOAD Student,

Subject,

Student#

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Main);

Left Join (Main)

LOAD StudentNumber as Student#,

'Math' as Subject,

Grade

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Math);

Re: Searching for solution.

YoussefBelloum — Wed, 03 Oct 2018 15:51:21 GMT

do you get an error message ?

maybe you don't have the field Subject on the main table or it is null, anyway, just remove it from the main table load and try again

Re: Searching for solution.

userid128223 — Wed, 03 Oct 2018 16:06:31 GMT

Error field Subject not found. It did not work when subject field was empty. It did not work when i populated subject with 1 field.

Re: Searching for solution.

userid128223 — Wed, 03 Oct 2018 17:55:34 GMT

what if i want to keep subject field in first table. How can i make this work.

Re: Searching for solution.

MarcoWedel — Wed, 03 Oct 2018 21:50:15 GMT

Hi,

maybe one solution could be:

tabMain:
LOAD StudentNumber as Student#,
    'Science' as Subject,
    Grade
FROM [https://community.qlik.com/thread/315776] (html, codepage is 1252, embedded labels, table is @2);

LOAD Number as Student#,
    'Math' as Subject,
    Grade
FROM [https://community.qlik.com/thread/315776] (html, codepage is 1252, embedded labels, table is @3);

Join
LOAD Student, 
    Student#
FROM [https://community.qlik.com/thread/315776] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Searching for solution.

MarcoWedel — Fri, 05 Oct 2018 19:24:39 GMT

What about Karl and his English class from your Main table?

You didn't expect him to be present in your final result table, but still want to keep the subject field from the Main table?

Can you describe how this result would look like, i.e. other than your initial final result?