topic Re: How to create new column based on two tables in QlikView

How to create new column based on two tables

diane_yu — Fri, 17 Apr 2015 20:07:49 GMT

Hello all,

I have two tables that are already joined. What I need is that create a new column or over write existing column based on the criteria.

These two data is one QVD file

Table 1 (UID, APV,....)

Table 2 (UID, COST_A,...)

I'm using IF(APV='A', 71.88, COST_A) but I don't think I'm doing it correctly.

If Table A APV='A' then I want to update the Table 2 COST_A column with $71.88 rate, otherwise use existing COST_A rate.

Any recommendations are welcome.

Thank you.

Re: How to create new column based on two tables

sunny_talwar — Fri, 17 Apr 2015 20:13:25 GMT

You can try doing like this:

Table1:

LOAD yourFields

FROM xyz;

Join Table1

LOAD yourFields

FROM abc;

Table:

LOAD *,

If(APV = 'A', 71.88, COST_A) as COST_A (if you want to replace COST_A or something else if you want to create a new column)

LOAD *

Resident Table1;

HTH

Best,

Sunny

Re: How to create new column based on two tables

maxgro — Fri, 17 Apr 2015 20:18:40 GMT

maybe with a mapping table

maptable2:

Mapping load * inline [

UID, COST_A

1, 91

2, 92

3, 93

4, 94

];

table1:

load

UID,

APV,

IF(APV='A', 71.88, applymap('maptable2', UID)) as CALC_FIELD;

load * inline [

UID, APV

1, 'A'

2, 102

3, 'A'

4, 104

];

Re: How to create new column based on two tables

diane_yu — Mon, 20 Apr 2015 16:08:03 GMT

Thank you Massimo/Sunindia,

I was able to modify scripts based on your information and this is what I did to make it work but it takes very long to load the data. I have over 13M rows.

COST_A:

LOAD UID, ,,,, COST_A
FROM [..\QVD\COST_A.QVD] (QVD) ;

APV:

LOAD UID, ,,,,,, APV
FROM [..\QVD\APV.DVD] (QVD);

CPE_Map:
mapping LOAD distinct UID,
COST_A as reimbursement
resident COST_A;

drop table COST_A;

Rev_APV:

NoConcatenate
LOAD *,

if(APV='APV',77.81, APPLYMAP('CPE_Map',UID,Cost_Per_Enc)) as reimbursement_rate
resident APV;

drop table APV;

RENAME Table rev_APV to APV;

Do you have any recommendation for faster loading?

Thanks.

Re: How to create new column based on two tables

sunny_talwar — Mon, 20 Apr 2015 16:19:30 GMT

May be try it like this (Have not tested so may or may not work)

CPE_Map:
Mapping LOAD distinct UID,
COST_A as reimbursement
FROM [..\QVD\COST_A.QVD] (QVD) ;

APV:

LOAD UID, ,,,,,, APV,

If(APV='APV', 77.81, APPLYMAP('CPE_Map',UID,Cost_Per_Enc)) as reimbursement_rate

FROM [..\QVD\APV.DVD] (QVD);

Best,

Sunny