Searching for solution.

[userid128223]

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

[YoussefBelloum]

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

[userid128223]

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);

[YoussefBelloum]

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

[userid128223]

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.

[userid128223]

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

[MarcoWedel]

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

[MarcoWedel]

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?