Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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
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
];
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
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