Calculated fields

[#1]

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.

[#2]

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

[rajeshvaswani77]

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

[#4]

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

[its_anandrjs]

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

[#6]

Hi Jagan,

Thank you the code.

But percentage is coming same for all the rows.

Please help.

[its_anandrjs]

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

[#8]

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

[saumyashah90]

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.

[#10]

Hi Saumya,

I am getting the error as shown.

[#11]

Hi,

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