topic Re: Calculation in staright Table in QlikView

Calculation in staright Table

Thu, 11 Feb 2016 12:06:38 GMT

Hi All,

I want to display data in straight table and data is in below format.

I am getting values in point format after calculating it from different fields and basis on this value (ans of that calculation) I want to map it with below table.

for example if ans of calculation is = to 1.6 it must display 2, if ans is less than 1.5 it must display 1 and so on

Value	Points
< = 1.5	1
1.6	2
1.7	3
>=1.8	4

Please help me in getting this output.

Re: Calculation in staright Table

MayilVahanan — Thu, 11 Feb 2016 12:21:11 GMT

Try like ths

Points

=If(Column(1) <= 1.5, 1.

if(Column(1) = 1.6, 2,

If(Column(1) = 1.7, 3, 4)))

And Hide the Value Column in presentation tab, if its not required

Hope it helps you

Re: Calculation in staright Table

Thu, 11 Feb 2016 12:30:39 GMT

Hi, I have tried this.....but the fact is that I have too many(say 100) of such values to map.. So that will be a lot of work ..I guess.. Is there any other way..in which we can achieve this..

Re: Calculation in staright Table

sunny_talwar — Thu, 11 Feb 2016 12:52:30 GMT

Is the mapping going to be dynamic? If not then do this in the script using Mapping Load may be

Re: Calculation in staright Table

maternmi — Thu, 11 Feb 2016 13:03:46 GMT

Hey,

if you want to avoid results in point format e.g. 1.6 then you can use the round formula, eg round(6/4), the result is 2 not 1.5.

I hope it will helps you!

Michael

Re: Calculation in staright Table

avinashelite — Thu, 11 Feb 2016 13:30:28 GMT

if you have limited set of values to map then you could create a inline table and map the data using the applymap function.

Temp:

Mapping LOAD * inline

[

Value, Points

1,1

1.5,1

1.7,3

...

];

LOAD

applymap('Temp',Value,null()) as Round_value

from table ;

Re: Calculation in staright Table

Thu, 11 Feb 2016 15:01:13 GMT

Hi Everyone,

I my scenario, i have 3 excel source in different data format

excel one contains data in the format

[SR]:

Manager Name	Employee Name	Month	Quarter	Comm Type
Hardik	Priyanka	1	1	First
Hardik	Monika	1	1	Pre
Hardik	Sarvesh	1	1	First
Hardik	Afsar	1	1	First
Hardik	Ram	1	1	Pre
Hardik	Sita	1	1	Pre
Anis	Akhilesh	1	1	Pre
Anis	Ramesh	1	1	First
Anis	Saravana	1	1	First
Anis	Prashant	1	1	First
Anis	Prathamesh	1	1	Pre
Anis	Aman	1	1	Pre
Anis	Rahul	1	1	Pre
Hardik	Priyanka	2	1	First
Hardik	Monika	2	1	First
Hardik	Sarvesh	2	1	Pre
Hardik	Afsar	2	1	Pre
Hardik	Ram	2	1	Pre
Hardik	Sita	2	1	Pre
Anis	Akhilesh	2	1	Pre
Anis	Ramesh	2	1	First
Anis	Saravana	2	1	Pre
Anis	Prashant	2	1	Pre
Anis	Prathamesh	2	1	First
Anis	Aman	2	1	First
Anis	Rahul	2	1	First

in the above table we have to load where comm type = 'Pre'

[Paid]:

Manager Name	Employee Name	Month	Quarter	count
Hardik	Priyanka	1	1	1
Hardik	Sarvesh	1	1	1
Hardik	Ram	1	1	0
Hardik	Sita	1	1	1
Anis	Akhilesh	1	1	0
Anis	Ramesh	1	1	1
Anis	Saravana	1	1	1
Anis	Prashant	1	1	0
Anis	Prathamesh	1	1	1
Anis	Aman	1	1	1
Anis	Rahul	1	1	0
Hardik	Priyanka	2	1	1
Hardik	Sarvesh	2	1	1
Hardik	Ram	2	1	0
Hardik	Sita	2	1	1
Anis	Akhilesh	2	1	1
Anis	Ramesh	2	1	0
Anis	Saravana	2	1	0
Anis	Prashant	2	1	0
Anis	Prathamesh	2	1	0
Anis	Aman	2	1	0
Anis	Rahul	2	1	1
Hardik	Payal	3	1	0
Hardik	Mohan	3	1	4

and score table as shown in first message.

Score of every employee is calculated as follows Sum(Count) / count(Comm Type).

I am unable to achieve this in backend and that the reason i calculated it straight table and want to map each partners value in frontend