assigning values from one table to other in script

[#1]

Hi Experts,

i have the following tables.

BRANCH:

LOAD BRCODE,

           BRNAME  as branchname,

           BRANCHMANAGER,

           RSMNAME

FROM

QVD\BRANCH.qvd

(qvd);

CallDetails:

LOAD DECDDT,

           BRNAME as branchname,

          Answered,

         Unanswered,

         Outgoing

FROM

QVD\data.qvd

(qvd);

i have to assign the values of BRCODE( from BRANCH qvd ) to the branchname(in data qvd) in the script itself.(may be like vlookup in excel. the branchname in branch table should look into the branchname in calldetails table and if it got matched, then the BRCODE should get assigned to the branchname in calldetails table)

how can i do that.

please give some suggestions.

its urgent. plsssssssssssssssssssssss.

[Gysbert_Wassenaar · Accepted solution]

MYMAP:

MAPPING LOAD

          BRNAME  as branchname,

          BRCODE

FROM

QVD\BRANCH.qvd

(qvd);

BRANCH:

LOAD BRCODE,

           BRNAME  as branchname,

           BRANCHMANAGER,

           RSMNAME

FROM

QVD\BRANCH.qvd

(qvd);

CallDetails:

LOAD DECDDT,

           BRNAME as branchname,

          ApplyMap('MYMAP', BRNAME ) as brcode

          Answered,

         Unanswered,

         Outgoing

FROM

QVD\data.qvd

(qvd);

[robert_mika]

BRANCH:

LOAD BRCODE,

           BRNAME  as branchname,

           BRANCHMANAGER,

           RSMNAME

FROM

QVD\BRANCH.qvd

(qvd);

drop fields branchname,

CallDetails:

LOAD DECDDT,

           BRNAME as branchname,

lookup('BRCODE','BRNAME',BRNAME,'BRANCH') as brcode,

          Answered,

         Unanswered,

         Outgoing

FROM

QVD\data.qvd

(qvd);

[#4]

hi robert,

it is showing 0 as brcode for all branchnames.

anyother suggestions plsssssssssss

[robert_mika]

Did you try Gysbert solution?

Otherwise I may need to see your qwv

[#6]

hi

its not working.

it is showing BRNAME   values in brcode field which is wrong.

BRCODE is a numeric value. its the branch code for different branch names.

i want the BRCODE values from branch table to get added to the calldetails table's branchnames.

for ex.

in branch table, i have

BRCODE      branchname

0001               london

0002               swindon

0003               Newbury

and so on

in call details table, i have

branchname              date               answered        unanswered

newbury               01/01/2015          10                         2

london                    02/03/2015          5                         0

i have to assign the BRCODE  as a new field with the branchname in calldetails table.

please give suggestions.

its very urgent plssssssssssssss

[#7]

hi

anybody help plssssssssss

[MarcoWedel]

please post sample data, otherwise it will be difficult to find your issues...

regards

Marco

[ramoncova06]

if your data is identical to the example you posted here then the issue is most likely with the cases not matching

just added the upper() function to your fields and that should do, here is gwassenaar‌code updated with the upper

MYMAP:

MAPPING LOAD

          upper(BRNAME)  as branchname,

          BRCODE

FROM

QVD\BRANCH.qvd

(qvd);

BRANCH:

LOAD BRCODE,

          BRNAME  as branchname,

          BRANCHMANAGER,

          RSMNAME

FROM

QVD\BRANCH.qvd

(qvd);

CallDetails:

LOAD DECDDT,

          BRNAME as branchname,

          ApplyMap('MYMAP', upper(BRNAME) ) as brcode

          Answered,

        Unanswered,

        Outgoing

FROM

QVD\data.qvd

(qvd);