Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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?
Hi Paul,
See if ApplyMap() works to suit your requirement.
Regards,
JP
plenty of Docs,tuts on it: here is one blog:
http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap
May be like:
Generic
Load
*;
Load
DataID,
If(FieldID=5, 'Client Name', If(FieldID=6, 'Customer Number', 'Seller')) as Gen,
DataFrom
[http://community.qlik.com/thread/142386]
(html, codepage is 1252, embedded labels, table is @1);
Applymap function will do.. just typw applymap in help of qlikview u ll understand
Wouldnt we need some complicated double version of apply map? We're saying vlookup 88 & 5 etc.
This is what i have so far
Tably:
MAPPING LOAD
DataID & ' ' & FieldID as unique,
Data
FROM
(
Tablyyy34:
LOAD
FieldID,
Applymap('Tably',Data) as unique
FROM
(
(I'm practicing with an excel document)