topic Re: Field as row in Table object in QlikView

Field as row in Table object

chnarayanrao — Mon, 19 Nov 2018 15:46:44 GMT

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..

Re: Field as row in Table object

jobsonkjoseph — Mon, 19 Nov 2018 16:30:37 GMT

Hi, It is better to go with Pivot Table option.

Re: Field as row in Table object

chnarayanrao — Mon, 19 Nov 2018 16:50:24 GMT

Any idea How to do this? I have AVGMarks1, AvgMArks2 etc as fileds. I need to show for ID , for a semister, i need to show AvgMarks.

Re: Field as row in Table object

vamsee — Mon, 19 Nov 2018 16:55:38 GMT

can you attach some sample source data

Re: Field as row in Table object

chnarayanrao — Mon, 19 Nov 2018 17:24:19 GMT

Hi Vamsee, it's available in my original post. Attaching again.

Re: Field as row in Table object

vamsee — Mon, 19 Nov 2018 17:32:34 GMT

Was requesting you for the source data sample, i.e. with the fields

AVGMarks1.AVGMarks2 etc.

The attachment is the output expected right.

Re: Field as row in Table object

vamsee — Mon, 19 Nov 2018 17:46:11 GMT

If that is indeed your source data and I misinterpreted your question then please find the attached qvw

Re: Field as row in Table object

chnarayanrao — Tue, 20 Nov 2018 16:46:41 GMT

Hi Vamsee,

Thanks for this, appreciate your help.

I am posting now the source data. In the attached excel ,here has expected result(sheet 1) and source data in sheet 2&3. Could you please help me for this.

Re: Field as row in Table object

vamsee — Tue, 20 Nov 2018 19:32:32 GMT

Please find attached.

Solution: CrossTable (Semester, Marks, 3)

Re: Field as row in Table object

chnarayanrao — Wed, 21 Nov 2018 11:25:11 GMT

Hi Vamsee,

Thanks for this. Could you please place the script here? I couldn't open the qvw at the moment.

Re: Field as row in Table object

vamsee — Wed, 21 Nov 2018 17:57:51 GMT

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

EXIT SCRIPT;

Re: Field as row in Table object

chnarayanrao — Wed, 21 Nov 2018 18:19:17 GMT

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

Re: Field as row in Table object

vamsee — Mon, 26 Nov 2018 14:20:38 GMT

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.

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

Please find the attached screenshot.

Re: Field as row in Table object

chnarayanrao — Mon, 26 Nov 2018 20:04:22 GMT

Hi Vamsee,

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

Re: Field as row in Table object

vamsee — Tue, 27 Nov 2018 15:43:28 GMT

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

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