swapping the sales records from one code to other

[#1]

Hi experts,

i have the following qvds.

In the Branch table, i have to swap the BRCODE like this.

Old    New

10      12

08      14

31       01

Only the BRCODE has to be swapped not the BRNAME.

When i do this, i have to get all other details like Answered, Amount, etc (from the respective tables) under the new BRCODE.

Branch:

LOAD BRCODE,

     BRNAME

FROM

QVD\K8_BRANCH.qvd

(qvd);

Calls:

LOAD BRCODE,

     DECDDT,

     BRNAME,

     Answered

FROM

QVD\OLD_DATA.qvd

(qvd);

concatenate

LOAD DECDDT,

     BRNAME,

     Lookup('BRCODE','BRNAME', BRNAME,'Calls') as BRCODE,

      Answered

FROM

QVD\CallDetails.qvd

(qvd)

Sales:

LOAD  DECDDT,

            Amount,

            Type,

            BRCODE

FROM

QVD\Sales.qvd

(qvd);

How can i do this.... pls guide me....

its uregentttttttttttt plssssssssss.

[sgrice]

do not use lookup use applymap

NewCODE_MAP:

mapping

load * inline[

myFrom , myTo

10,12

08,14

31,01];

then in your code instead of

Lookup('BRCODE','BRNAME', BRNAME,'Calls') as BRCODE,

use

applymap('NewCODE_MAP',BRCODE,BRCODE) as BRCODE,

[#3]

Hi

i see 2 solutions

either you are sure BRNAME has a unique value per BRCODE and you may use it as a key like for example autonumber(BRNAME) as Key_CodeTransiton

and then you can load your old and new table by JOIN based upon Key_CodeTransiton

or you can use

Map_table:

MAPPING LOAD  * inline [

Old    New

10      12

08      14

31       01

];

if one input value gives ONLY ONE outputvalue

anf then in the script use

applymap('MapTable', OldValue, 'DefaultValue if Oldvalue not exists')     as NEWCode

best regards

Chris

[#4]

Hi Chris,

the BRNAME is same for the old and new BRCODE.

i have tried the mapping already.

its doing the change only on BRCODE.

it is not transferring other records to the new BRCODE.

Pls give some suggestions with script.

plsssssssss

[#5]

hi steven,

Lookup is not for this swapping.

that is taking the BRCODE from one table to other by looking up the BRNAME.

[#6]

HI

Tmp:

LOAD

autonumber(BRNAME)     as KEY,

BRCODE as     oldCODE

FROM Table1;

JOIN

LOAD

autonumber(BRNAME)     as KEY,

BRCODE as     newCODE

FROM Table2;

// Now you have a match between old and new, you can use into a mapping table

MapTable:

LOAD oldCode, NewCode resident tmp;

drop table Tmp;

then when you load your qvd use

LOAD

applymap('MapTable' BRCODE, 'unknown')     as BRCODE  // wil replace Old code by new code

....

best regards

Chris

[#7]

hi chris,

thanks for your response.

can u pls tell me which table should i take as Table1 & Table2.

is it thro inline with old and new codes like this??

Table1:

LOAD * INLINE [

    BRCODE

    31

   08

    10

];

Table2:

LOAD * INLINE [

    BRCODE

    01

    14

    12

];

pls help

[#8]

PRRAJENDRAN

can you send your script ?

Chris

[#9]

My script below..

Branch:

LOAD BRCODE,

     BRNAME

FROM

QVD\K8_BRANCH.qvd

(qvd);

Calls:

LOAD BRCODE,

     DECDDT,

     BRNAME,

     Answered

FROM

QVD\OLD_DATA.qvd

(qvd);

concatenate

LOAD DECDDT,

     BRNAME,

     Lookup('BRCODE','BRNAME', BRNAME,'Calls') as BRCODE,

      Answered

FROM

QVD\CallDetails.qvd

(qvd)

Sales:

LOAD  DECDDT,

            Amount,

            Type,

            BRCODE

FROM

QVD\Sales.qvd

(qvd);

[#10]

Where is information about old and new codes ?

Chris