Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

MDB Vlookup?

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?

1 Solution

Accepted Solutions
jpenuliar
Partner - Specialist III
Partner - Specialist III

Hi Paul,

See if ApplyMap() works to suit your requirement.

Regards,

JP

View solution in original post

6 Replies
jpenuliar
Partner - Specialist III
Partner - Specialist III

Hi Paul,

See if ApplyMap() works to suit your requirement.

Regards,

JP

jpenuliar
Partner - Specialist III
Partner - Specialist III

tresesco
MVP
MVP

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);

vinay_hg
Creator III
Creator III

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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)