Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated fields

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.

28 Replies
rajeshvaswani77
Specialist III
Specialist III

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
Master III
Master III

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
Partner - Specialist II
Partner - Specialist II

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
Champion III
Champion III

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

Not applicable
Author

I got this error:

Field not found - <SALARY>

Please correct me.

Not applicable
Author

I tried this. Still the same error.

jagan
Partner - Champion III
Partner - Champion III

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
Master III
Master III

Use like this:

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

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

Thanks.,

AS

amit_saini
Master III
Master III

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

Thanks,
AS