topic Re: custom column from two table columns in QlikView

custom column from two table columns

qlikviewwizard — Wed, 06 May 2015 11:42:35 GMT

Hi All,

I have two tables as shown below.

TableA:

Load * inline [

EMPNO,A,B,SAL

EMPNO1,A1,B1,10

EMPNO2,A2,B2,20

EMPNO3,A3,B3,30

EMPNO4,A4,B4,40

EMPNO5,A5,B5,50

];

TableB:

Load * inline [

EMPNO,COMM,C

EMPNO1,10,C1

EMPNO2,20,C2

EMPNO3,30,C3

EMPNO4,40,C4

EMPNO5,50,C5

];

I want to derive new column percentage with COMM and SAL.

Required column: COMM/SAL.

How to achieve this?

Thanks in advance.

Re: custom column from two table columns

mrossoit — Wed, 06 May 2015 11:53:30 GMT

TableA:

Load * inline [

EMPNO,A,B,SAL

EMPNO1,A1,B1,10

EMPNO2,A2,B2,20

EMPNO3,A3,B3,30

EMPNO4,A4,B4,40

EMPNO5,A5,B5,50

];

join

Load * inline [

EMPNO,COMM,C

EMPNO1,10,C1

EMPNO2,20,C2

EMPNO3,30,C3

EMPNO4,40,C4

EMPNO5,50,C5

];

TableAB:

load *, COMM/SAL as percentage

resident TableA;

drop table TableA;

Regards

Re: custom column from two table columns

Anonymous — Wed, 06 May 2015 11:54:35 GMT

Hi,

Use mapping load in your script

Load * inline [
EMPNO,A,B,SAL
EMPNO1,A1,B1,10
EMPNO2,A2,B2,20
EMPNO3,A3,B3,30
EMPNO4,A4,B4,40
EMPNO5,A5,B5,50
];
Mapping_Table:
Mapping Load EMPNO,SAL Resident TableA;

TableB:
load *, COMM/ApplyMap('Mapping_Table',EMPNO) as Percentage;
Load * inline [
EMPNO,COMM,C
EMPNO1,10,C1
EMPNO2,20,C2
EMPNO3,30,C3
EMPNO4,40,C4
EMPNO5,50,C5
];

Regards,

Greeshma

Re: custom column from two table columns

petter — Wed, 06 May 2015 12:00:03 GMT

I would do the calculation in the UI and do:

TABLE:

Load * inline [

EMPNO,A,B,SAL

EMPNO1,A1,B1,10

EMPNO2,A2,B2,20

EMPNO3,A3,B3,30

EMPNO4,A4,B4,40

EMPNO5,A5,B5,50

];

JOIN

Load * inline [

EMPNO,COMM,C

EMPNO1,10,C1

EMPNO2,20,C2

EMPNO3,30,C3

EMPNO4,40,C4

EMPNO5,50,C5

];

Re: custom column from two table columns

qlikviewwizard — Wed, 06 May 2015 12:08:21 GMT

petter-s

How would you do? Please help to provide code.

Re: custom column from two table columns

petter — Wed, 06 May 2015 12:10:43 GMT

The JOIN has been done as I indicated in the Load Script I posted.

The calculation in a chart would simply be:

COMM/SAL as an expression where you need it to be calculated ....

Re: custom column from two table columns

qlikviewwizard — Wed, 06 May 2015 14:06:02 GMT

Thank you All.