Problems with lookup table or vlookup equivalent

[#1]

Hi

Can someone please help? I have spent weeks trying to do something that is no doubt very, very easy.

I have a table of machines in which if the serial number = link reference then it is the master machine that holds all the billing information for all the machines with that link reference.  Where the serial number doesn't equal the link reference there is currently no data in the relevant fields.  I have managed to create a temporary table that holds the link reference, meter number and the charge information, but for the life of me can not get it to repopulate the machines table to fill in the blanks.  I know about mapping tables, but because I have more than 1 field to evaluate I understand this is not possible.

MACHINES TABLE

ACCOUNT     SERIAL     LINK_REFERENCE     METER     CHARGE

1                    1234          1222                         1               -

1                    1234          1222                         2               -

1                    1234          1234                         1               0.05

1                    1234          1234                         2               5.0

1                    1234          1279                         1               -

1                    1234          1279                         2               -

1                    1234          1561                         1               -

1                    1234          1561                         2               -

LINKED_BILLING TABLE

LINK_REFERENCE     METER     CHARGE

1234                           1               0.05

1234                           2               5.0

Any help would be gratefully received.

Thanks

Paul

[Gysbert_Wassenaar]

See attached qvw.

[#3]

You can get by simply join the tables on right keys. or you can create the composite key in the mapping table .

Generally in the Mapping table have 2 fields (KEY, VALUE) only. In your case the KEY have more than 1 field. So just create the KEY field with composite key (Combination of more than 1 field) like below:

MAP_CHARGE:

MAPPING

LOAD LINK_REFERENCE &'-'&METER AS KEY , CHARGE INLINE [

LINK_REFERENCE , METER , CHARGE

1234, 1, 0,05

1234, 2, 5.0

];

MASTER_MACHINES:

LOAD ACCOUNT,

          SERIAL,

          LINK_REFERENCE,

          METER ,

          ApplyMap('MAP_CHARGE', SERIAL & '-' & METER , 0) AS CHARGE

FROM SOURCE ;