Problems with lookup table or vlookup equivalent

Thu, 19 Dec 2013 19:14:56 GMT

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

Re: Problems with lookup table or vlookup equivalent

Gysbert_Wassenaar — Thu, 19 Dec 2013 19:21:20 GMT

See attached qvw.

Re: Problems with lookup table or vlookup equivalent

Thu, 19 Dec 2013 20:43:58 GMT

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 ;

topic Re: Problems with lookup table or vlookup equivalent in QlikView

Problems with lookup table or vlookup equivalent

Re: Problems with lookup table or vlookup equivalent

Re: Problems with lookup table or vlookup equivalent