6 Replies Latest reply: Nov 17, 2014 10:51 AM by Paul Wonford RSS

    MDB Vlookup?

    Paul Wonford

      Hi guys,

       

      I have a Qlikview document which pulls data from an access database. The key identifier in this data is the customer number, this links everything together. The “Information” table is shown below

       

      DataID

      FieldID

      Data

      88

      5

      Paul’s Garage

      88

      6

      1

      88

      7

      Yes

      89

      5

      Steve’s Autos

      89

      6

      9

      89

      7

      Yes

      90

      5

      Grange motors

      90

      6

      16

      90

      7

      No

       

      • FieldID 5 is Customer name
      • FieldID 6 is Customer Number
      • FieldID 7 is Seller (indicates whether they just fix cars or if they sell them as well)

       

      My code is shown below

      Load *,

      If(FieldID = 5 or FieldID = 6 or FieldID = 7,[DataID],) as [Data ID],
      If(FieldID = 5,[Data],) as [Client Name],
      If(FieldID = 6,[Data],) as [Customer Number],

      If(FieldID = 7,[Data],) as [Seller];

      SQL Select

      DataID,
      FieldID,
      Data

      From [CustomerInfo];

       

      As you can see; the table allocates a unique identifier under the DataID column but the entire qlikview document is tied together using customer number. I want the unique identifier to be Field 6 so that the data shows as below

       

      Customer Number

      Client Name

      Seller

      1

      Paul’s garage

      Yes

      9

      Steve’s Autos

      Yes

      16

      Grange Motors

      No

       

      No matter what I do I can’t seem to get it to work. Do i need some kind of vlookup?

       

      Can anyone help?