topic Re: Calculated fields in QlikView

Calculated fields

Mon, 22 Dec 2014 07:55:30 GMT

Hi All,

I loaded EMP data by using the specified script:

EMP:

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

DEPTNO,

IF(COMM = 'NULL',0,COMM) as COMM,

Alt(COMM, '',0) as COMM1

FROM

[//.......QV\EMP Details.xlsx]

(ooxml, embedded labels, table is EMP);

Now I want create some calculated fields.

(SAL+COMM) as SALARY,

SALARY*12 as Total_SALARY,

(SALARY/Total_SALARY)*100 as Salary_Percentage

How to achieve this.

Thanks in advance.

Re: Calculated fields

rajeshvaswani77 — Mon, 22 Dec 2014 07:58:10 GMT

You have to do preceding load.

So you will do this.

EMP:

LOAD *,

(SAL+COMM) as SALARY,

SALARY*12 as Total_SALARY,

(SALARY/Total_SALARY)*100 as Salary_Percentage;

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

DEPTNO,

IF(COMM = 'NULL',0,COMM) as COMM,

Alt(COMM, '',0) as COMM1

FROM

[//.......QV\EMP Details.xlsx]

(ooxml, embedded labels, table is EMP);

thanks,

Rajesh Vaswani

Re: Calculated fields

amit_saini — Mon, 22 Dec 2014 08:01:45 GMT

Hi,

Try this:

EMP:

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

DEPTNO,

IF(COMM = 'NULL',0,COMM) as COMM,

Alt(COMM, '',0) as COMM1,

(SAL+COMM) as SALARY,

SALARY*12 as Total_SALARY,

(SALARY/Total_SALARY)*100 as Salary_Percentage

From

.....

Thanks,

Re: Calculated fields

manojkulkarni — Mon, 22 Dec 2014 08:01:59 GMT

EMP:

LOAD *, (SALARY/Total_SALARY)*100 as Salary_Percentage;

LOAD *,

(SAL+COMM) as SALARY,

SALARY*12 as Total_SALARY;

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

DEPTNO,

IF(COMM = 'NULL',0,COMM) as COMM,

Alt(COMM, '',0) as COMM1

Re: Calculated fields

its_anandrjs — Mon, 22 Dec 2014 08:03:37 GMT

Hi,

You can try also

TMPEMP:
LOAD EMPNO,
     ENAME,
     JOB,
     MGR,
     HIREDATE,
     SAL,
     DEPTNO,
     IF(COMM = 'NULL',0,COMM) as COMM,
     Alt(COMM, '',0) as COMM1
FROM
[//.......QV\EMP Details.xlsx]
(ooxml, embedded labels, table is EMP);

NewFinalTable:
Noconcatenate
Load *,
(SAL+COMM) as SALARY,
SAL*12 as Total_SALARY,
(SAL/(SAL*12))*100 as Salary_Percentage
Resident TMPEMP;

With some changes

Regards

Anand

Re: Calculated fields

Mon, 22 Dec 2014 08:04:37 GMT

I got this error:

Field not found - <SALARY>

Please correct me.

Re: Calculated fields

Mon, 22 Dec 2014 08:06:20 GMT

I tried this. Still the same error.

Re: Calculated fields

jagan — Mon, 22 Dec 2014 08:07:29 GMT

Hi,

Try this script

EMP:

LOAD

SALARY * 12 AS Total_SALARY,

(SALARY/(SALARY * 12))*100 as Salary_Percentage;

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

DEPTNO,

Alt(COMM, 0) as COMM,

Alt(COMM, '',0) as COMM1,

(SAL+Alt(COMM, 0)) as SALARY

FROM

[//.......QV\EMP Details.xlsx]

(ooxml, embedded labels, table is EMP);

Regards,

Jagan.

Re: Calculated fields

amit_saini — Mon, 22 Dec 2014 08:08:53 GMT

Use like this:

((SAL+COMM))*12 as Total_SALARY,

((SAL+COMM)/((SAL+COMM))*12)*100 as Salary_Percentage

Thanks.,

Re: Calculated fields

amit_saini — Mon, 22 Dec 2014 08:09:43 GMT

<SALARY> won't work as it is alias not field.

Thanks,
AS

Re: Calculated fields

Mon, 22 Dec 2014 08:10:42 GMT

Hi Anand,

I am getting the below error :

Field not found - <SALARY>

NewFinalTable:

noconcatenate

Load *,

(SAL+COMM) as SALARY,

SAL*12 as Total_SALARY,

(SAL/(SALARY*12))*100 as Salary_Percentage

Resident TMPEMP

Re: Calculated fields

rajeshvaswani77 — Mon, 22 Dec 2014 08:12:24 GMT

Hi,

Try this.

EMP:
LOAD *,(SALARY/Total_SALARY)*100 as Salary_Percentage;
LOAD *,SALARY*12 as Total_SALARY;
LOAD *,(SAL+COMM) as SALARY;
LOAD EMPNO,
     ENAME,
     JOB,
     MGR,
     HIREDATE,
     SAL,
     DEPTNO,
     IF(COMM = 'NULL',0,COMM) as COMM,
     Alt(COMM, '',0) as COMM1
FROM
[//.......QV\EMP Details.xlsx]
(ooxml, embedded labels, table is EMP);

thanks,

Rajesh Vaswani

Re: Calculated fields

Mon, 22 Dec 2014 08:12:30 GMT

Load *, Sum(SAL+(IF(COMM = 'NULL',0,COMM)) as Salary

,Sum(SAL+(IF(COMM = 'NULL',0,COMM))*12 as TotalSalary

,(Sum(SAL+(IF(COMM = 'NULL',0,COMM))/(Sum(SAL+(IF(COMM = 'NULL',0,COMM))*12))*100 as SalaryPercentage From Your table Group by

EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

DEPTNO,

COMM

;

Please try this script

Re: Calculated fields

its_anandrjs — Mon, 22 Dec 2014 08:12:44 GMT

Hi,

And most specific if you use it like

TMPEMP: 
LOAD EMPNO, 
     ENAME, 
     JOB, 
     MGR, 
     HIREDATE, 
     SAL, 
     DEPTNO, 
     IF(COMM = 'NULL',0,COMM) as COMM, 
     Alt(COMM, '',0) as COMM1,
     SAL+COMM AS SALARY,
     (SAL+COMM)*12 AS Total_SALARY,
     ((SAL+COMM) / (SALARY*12)) * 100 AS Salary_Percentage

FROM 
[//.......QV\EMP Details.xlsx] 
(ooxml, embedded labels, table is EMP);

Regards

Anand

Re: Calculated fields

Mon, 22 Dec 2014 08:13:54 GMT

Hi Jagan,

Thank you the code.

But percentage is coming same for all the rows.

Please help.

Re: Calculated fields

its_anandrjs — Mon, 22 Dec 2014 08:16:09 GMT

Then try this

TMPEMP: 
LOAD EMPNO, 
     ENAME, 
     JOB, 
     MGR, 
     HIREDATE, 
     SAL, 
     DEPTNO, 
     IF(COMM = 'NULL',0,COMM) as COMM, 
     Alt(COMM, '',0) as COMM1,
     SAL+COMM AS SALARY,
     (SAL+COMM)*12 AS Total_SALARY,
     ((SAL+COMM) / ((SAL+COMM)*12)) * 100 AS Salary_Percentage

FROM 
[//.......QV\EMP Details.xlsx] 
(ooxml, embedded labels, table is EMP);

Regards

Anand

Re: Calculated fields

Mon, 22 Dec 2014 08:29:16 GMT

Hi kabilan180591,

I am getting below error:

Error in expression:

Nested aggregation not allowed

EMP:

Load *, Sum(SAL+(IF(COMM = 'NULL',0,COMM))) as Salary

,Sum(SAL+(IF(COMM = 'NULL',0,COMM))*12) as TotalSalary

,(Sum(SAL+(IF(COMM = 'NULL',0,COMM))/(Sum(SAL+(IF(COMM = 'NULL',0,COMM))*12))*100 as SalaryPercentage

FROM

[//........QV\EMP Details.xlsx]

(ooxml, embedded labels, table is EMP)

group by

EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

DEPTNO

My code:

EMP:

Load *, Sum(SAL+(IF(COMM = 'NULL',0,COMM))) as Salary

          ,Sum(SAL+(IF(COMM = 'NULL',0,COMM))*12) as TotalSalary

          ,(Sum(SAL+(IF(COMM = 'NULL',0,COMM))/(Sum(SAL+(IF(COMM = 'NULL',0,COMM))*12))*100 as SalaryPercentage



FROM

[//...........QV\EMP Details.xlsx]

(ooxml, embedded labels, table is EMP)

group by

EMPNO,

     ENAME,

     JOB,

     MGR,

     HIREDATE,

     DEPTNO;

LOAD EMPNO,

    ENAME,

    JOB,

    MGR,

    HIREDATE,

    SAL,

    DEPTNO,

    COMM

   FROM

[//\EMP Details.xlsx]

(ooxml, embedded labels, table is EMP);

EXIT Script;

data:

EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800	NULL	20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	2-Apr-81	2975	NULL	20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	1-May-81	2850	NULL	30
7782	CLARK	MANAGER	7839	9-Jun-81	2450	NULL	10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000	NULL	20
7839	KING	PRESIDENT	NULL	17-Nov-81	5000	NULL	10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100	NULL	20
7900	JAMES	CLERK	7698	3-Dec-81	950	NULL	30
7902	FORD	ANALYST	7566	3-Dec-81	3000	NULL	20
7934	MILLER	CLERK	7782	23-Jan-82	1300	NULL	10

DEPT



DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

Re: Calculated fields

saumyashah90 — Mon, 22 Dec 2014 08:43:56 GMT

Hi JJr,

Load normal script and than in resident use your expression.it will work.

EMP:

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

DEPTNO,

IF(COMM = 'NULL',0,COMM) as COMM,

Alt(COMM, '',0) as COMM1

FROM

[//.......QV\EMP Details.xlsx]

(ooxml, embedded labels, table is EMP);

EMP_FINAL:

Load

EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

DEPTNO,

COMM,

COMM1,

(SAL+COMM) as SALARY,

SALARY*12 as Total_SALARY,

(SALARY/Total_SALARY)*100 as Salary_Percentage

resident EMP;

Drop Table EMP;

This will work for sure.

Re: Calculated fields

Mon, 22 Dec 2014 08:55:24 GMT

Hi Saumya,

I am getting the error as shown.

Re: Calculated fields

Mon, 22 Dec 2014 08:57:27 GMT

Hi,

Based on your explanation, it should be same for all rows. i.e. (1/12 = 8.33). Explain your requirement.