Field as row in Table object

[chnarayanrao]

I have below fields to create a table object in Qlikview. ID, Name, Subject,AVGmarks,Sem1,Sem2,Sem3.

I want to display the data as per the attached format. Could you please help ,how I can achieve this. AVGmarks field should be displayed as row to show the avg marks for each semister..

[vamsee]

CrossTable (Semester, Marks, 3)
LOAD ID,
Name,
Subject,
Sem1,
Sem2,
Sem3,
Sem4
FROM
[Marks (1).xlsx]
(ooxml, embedded labels, table is Sheet2);

EXIT SCRIPT;

[chnarayanrao]

Hi Vamsee,

 The expected output is shown in the Sheet1. The Marks1 and MArks2 data should be joined or linked to to get the expected output. The challange im facing is how to show the MarksAVG1,MarksAVG2 for each Sem1,2..etc respectively.

Marks1:
LOAD ID,
[Student Name],
Subject,
Sem1,
Sem2,
Sem3,
Sem4
FROM
[Marks.xlsx]
(ooxml, embedded labels, table is Sheet2);

Marks2:
LOAD ID,
MarksAVG1,
MarksAVG2,
MarksAVG3,
MarksAVG4
FROM
[Marks.xlsx]
(ooxml, embedded labels, table is Sheet3);

[vamsee]

As far as I understand w.r.t our previous discussions and excel sample provided

Sheet2 is source data.

Sheet1 is the output view required.

Sheet3 is a view of averages acquired from sheet2.

1. So use the above code to fetch data from sheet2.
2. Create a pivot table
3. Show partial sums for the column subject
4. To achieve Sheet3 view remove the dimensions Name and Subject.

Please find the attached screenshot.

[chnarayanrao]

Hi Vamsee,

Sheet2 and Sheet 3 are source data. By using this data , I need output as per Sheet1.

[vamsee]

I don't get it. How are averages your source data?

Anyway, you can achieve the same averages from sheet2 itself.