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.

[saumyashah90]

My Bad,

Can you check this.If ot i have one more last option

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_1:

Load

EMPNO,

     ENAME,

     JOB,

     MGR,

     HIREDATE,

     SAL,

      DEPTNO,

COMM,

COMM1,

(SAL+COMM) as SALARY    

resident EMP;

Drop Table EMP;

EMP_2:

Load

EMPNO,

     ENAME,

     JOB,

     MGR,

     HIREDATE,

     SAL,

      DEPTNO,

COMM,

COMM1,

SALARY ,   

SALARY*12 as Total_SALARY

Resident EMP_1;

Drop Table EMP_1;

EMP_Final:

Load

EMPNO,

     ENAME,

     JOB,

     MGR,

     HIREDATE,

     SAL,

      DEPTNO,

COMM,

COMM1,

SALARY ,   

Total_SALARY,

(SALARY/Total_SALARY)*100 as Salary_Percentage

Resident EMP_2;

Drop Table EMP_2;

[jagan]

Hi,

The script is correct, you are getting the same number because you are dividing all row by 12, can you attach sample data and your expected output.

Regards,

Jagan.

[tyagishaila]

you can easily calculate these field by using expression, when you create Table/Chart.

It will be more easy and no error will come, by using this you will protect you from reload data again and again.

[#5]

Hi Jagan,

This is the required output.

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

[#6]

Hi,

Try this.

Employee:

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

Join (Employee)

LOAD Sum(Salary) AS TotalSalary Resident Employee;

Join (Employee)

LOAD EMPNO, (Salary/TotalSalary)*100 AS Percetage Resident Employee;

[Anonymous]

Hi,

you can't use the alias name in order to do a formula in the script.

SAL+ COMM  as SALARY is correct, but to have total_Salary you should write a formula with original field:

(SAL+COMM)*12 as Total_Salary and so on for the percentage.

Elena

[its_anandrjs]

Hi,

Then try this ways

masterData:
LOAD EMPNO,
     ENAME,
     JOB,
     MGR,
     HIREDATE,
     SAL,
     COMM,
     DEPTNO,
     SAL+COMM AS SALARY
FROM
[..\..\Data\EmpData.xlsx]
(ooxml, embedded labels, table is Sheet1);

left Join
LOAD
sum(SALARY) AS Total_Salary
Resident masterData;

Left Join
LOAD
EMPNO,
num((SALARY/Total_Salary),'#,##0.00%') as Percentage
Resident masterData;

And final output you get

Regards

Anand

[Anonymous]

Hi,

For huge data,complex calculations in UI will cause performance issue.

so its better to do calculations in script.

Regards

neetha

[Anonymous]

Hi Try this below code

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

Left Join(EMP)

Load

EMPNO

(SAL+COMM) as SALARY,

     SALARY*12 as Total_SALARY,

     (SALARY/Total_SALARY)*100 as Salary_Percentage

Resident table EMP;