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.

[rajeshvaswani77]

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

[amit_saini]

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

[manojkulkarni]

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

[its_anandrjs]

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

[#6]

I got this error:

Field not found - <SALARY>

Please correct me.

[#7]

I tried this. Still the same error.

[jagan]

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.

[amit_saini]

Use like this:

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

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

Thanks.,

AS

[amit_saini]

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

Thanks,
AS