Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

pzwonford
New Contributor II

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
Valued Contributor III

Re: MDB Vlookup?

Hi Paul,

See if ApplyMap() works to suit your requirement.

Regards,

JP

6 Replies
jpenuliar
Valued Contributor III

Re: MDB Vlookup?

Hi Paul,

See if ApplyMap() works to suit your requirement.

Regards,

JP

jpenuliar
Valued Contributor III

Re: MDB Vlookup?

MVP
MVP

Re: MDB Vlookup?

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
Contributor II

Re: MDB Vlookup?

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

pzwonford
New Contributor II

Re: MDB Vlookup?

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

pzwonford
New Contributor II

Re: MDB Vlookup?

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)

Community Browser