How to create new column based on two tables

[diane_yu]

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.

[sunny_talwar]

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

[maxgro · Accepted solution]

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

];

[diane_yu]

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.

Di

[sunny_talwar]

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