topic Re: Create Straight Table in QlikView

Create Straight Table

Mon, 24 Aug 2015 08:26:04 GMT

Hi All,

I have the following data,Based on this data can we prepare Straight table like this

My Data:-

Empid Grade Subject Score

1001 A English 76

1001 A Matha 52

1001 A Science 38

1102 B English 34

1102 B Matha 55

1102 B Science 36

1002 B English 54

1002 B Matha 35

1002 B Science 40

1101 A English 86

1101 A Matha 42

1101 A Science 58

Output:

Grade Score_Status Average_Score Employee_Count

A Y 59.1 2

B N 42.3 2

--I want to Grade,Score_Status are Dimensions and Average_Score,Employee_Count are Expression.

Here Score_Status is if(avg(Score)<=50,'N','Y'))

Regards

Sharath

Re: Create Straight Table

petter — Mon, 24 Aug 2015 08:38:19 GMT

Yes it is very straight forward actually:

Re: Create Straight Table

Mon, 24 Aug 2015 08:58:40 GMT

Hi Petter,

Please send Score_Status script to me

Re: Create Straight Table

Mon, 24 Aug 2015 09:01:04 GMT

Please send script for all Coloumns.

Re: Create Straight Table

qlikviewwizard — Mon, 24 Aug 2015 09:03:14 GMT

Hi,

Please check this.

Thank you.

Re: Create Straight Table

Mon, 24 Aug 2015 09:11:20 GMT

Hi,

i am using Qlikview personal edition. i can't open your QVW. Please tell the process steps..here

Re: Create Straight Table

petter — Mon, 24 Aug 2015 09:11:46 GMT

The dimension is simply the field Grade

The expressions are:

Score_Status:

If( Average_Score <= 50,'N','Y')

Average_Score:

Avg(Score)

Employee_Count:

Count(DISTINCT Empid)

Re: Create Straight Table

petter — Mon, 24 Aug 2015 09:12:54 GMT

The load script is:

Data:

LOAD * INLINE [

Empid|Grade|Subject|Score

1001|A|English|76

1001|A|Matha|52

1001|A|Science|38

1102|B|English|34

1102|B|Matha|55

1102|B|Science|36

1002|B|English|54

1002|B|Matha|35

1002|B|Science|40

1101|A|English|86

1101|A|Matha|42

1101|A|Science|58

] (delimiter is |);

Re: Create Straight Table

Anonymous — Mon, 24 Aug 2015 09:19:46 GMT

Hi Sharath,

Please try:

script:

MyData:

LOAD * Inline

[

Empid,Grade,Subject,Score

1001,A,English,76

1001,A,Matha,52

1001,A,Science,38

1102,B,English,34

1102,B,Matha,55

1102,B,Science,36

1002,B,English,54

1002,B,Matha,35

1002,B,Science,40

1101,A,English,86

1101,A,Matha,42

1101,A,Science,58

];

left join(MyData)

LOAD Empid,Grade, if(avg(Score)<=50,'N','Y') as Score_Status

Resident MyData

Group by Empid, Grade;

UI:

Straight table

Dimensions - Grade ,Score_Status

Expressions - avg(Score) ,count( DISTINCT Empid)

uncheck suppress zero values in presentation tab

Regards

Neetha

Re: Create Straight Table

Mon, 24 Aug 2015 09:26:03 GMT

I need Score_status as dimension and not expression

Re: Create Straight Table

petter — Mon, 24 Aug 2015 09:37:04 GMT

=Num(Aggr( Avg(Score) , Empid ) , '0.0%','.',',')

Re: Create Straight Table

qlikviewwizard — Tue, 25 Aug 2015 01:47:14 GMT

Hi Sharath,

Please follow Neetha_p solution. It works.