Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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,
AS
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
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
I got this error:
Field not found - <SALARY>
Please correct me.
I tried this. Still the same error.
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.
Use like this:
((SAL+COMM))*12 as Total_SALARY,
((SAL+COMM)/((SAL+COMM))*12)*100 as Salary_Percentage
Thanks.,
AS
<SALARY> won't work as it is alias not field.
Thanks,
AS