topic MDB Vlookup? in QlikView

MDB Vlookup?

Anonymous — Mon, 17 Nov 2014 10:16:11 GMT

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?

Re: MDB Vlookup?

jpenuliar — Mon, 17 Nov 2014 10:29:35 GMT

Hi Paul,

See if ApplyMap() works to suit your requirement.

Regards,

Re: MDB Vlookup?

jpenuliar — Mon, 17 Nov 2014 10:32:14 GMT

plenty of Docs,tuts on it: here is one blog:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Re: MDB Vlookup?

tresB — Mon, 17 Nov 2014 11:26:44 GMT

May be like:

Generic
Load
*;

Load
DataID,
If(FieldID=5, 'Client Name', If(FieldID=6, 'Customer Number', 'Seller')) as Gen,
Data

From
[http://community.qlik.com/thread/142386]
(html, codepage is 1252, embedded labels, table is @1);

Re: MDB Vlookup?

vinay_hg — Mon, 17 Nov 2014 11:56:01 GMT

Applymap function will do.. just typw applymap in help of qlikview u ll understand

Re: MDB Vlookup?

Anonymous — Mon, 17 Nov 2014 15:42:02 GMT

Wouldnt we need some complicated double version of apply map? We're saying vlookup 88 & 5 etc.

Re: MDB Vlookup?

Anonymous — Mon, 17 Nov 2014 15:51:19 GMT

This is what i have so far

Tably:

MAPPING LOAD

DataID & ' ' & FieldID as unique,
Data

FROM

(ooxml, embedded labels, table is Sheet1);

Tablyyy34:

LOAD
FieldID,
Applymap('Tably',Data) as unique

FROM

(ooxml, embedded labels, table is Sheet1);

(I'm practicing with an excel document)