Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gerhardl
Contributor II

Matching fields to data in different table

Hi,

I have a QV app that is tracking applications and transactions of a new company we have taken over. For the project we started with 25 "pilot" stores. In our application extract we only have branch numbers, but for my report I needed to add branch names as well. Because there were so few stores I just loaded this into my script using if statements - (if([Branch No]='837','Ackermans Bellville', if([Branch No]='793','Ackermans Canal Walk', etc. as [Branch Name]).

But now we have rolled out to the entire country and there are 560 stores. I am not loading this into my script obviously. I have an excel sheet with all of the branch numbers, store names and all other information. I need to load this entire document into qlikview and then in my application and transaction extracts I need to match the branch numbers to the store names in this spreadsheet. Kinda like a VLookup in excel would do.

My script currently consists of 3 comma delimited files:

  • Account Extract (all account holder details, including store of opening). This extract gets replaced every day.
  • Daily Transaction Extract (transactional data of all customers, including store number where they purchased). I use the where exists(Account No) statement to only load transactions of account holders in the above account extract. This file gets concatenated daily (not replaced).
  • Daily Application Extract (new applications received, either approved or declined) - with the store numbers of where they applied. Also concatenated daily.

So you can see in all 3 extracts there are fields showing the store number - for all of them I need to match this to a store name, using the excel spreadsheet that I want to load.

I have NO IDEA how to do this....

Regards,

Gerhard

Tags (1)
1 Solution

Accepted Solutions

Re: Matching fields to data in different table

Hello Gerhard,

It seems to me an example of one of the uses for ApplyMap() function, if I got you right

StoreCodeNameMap: // one map will do for all tables

MAPPING LOAD Code,

     Name

FROM File.xls;

AccountExtract:

LOAD *,

     ApplyMap('StoreCodeNameMap', Code, 'Name not found') AS Extract_Store_Name

     // "Name not found" literal above will be returned should no code match the mapping table

     // Making easier to identify where the problem is

FROM Extracts.Source;

Transactions:

LOAD *,

     ApplyMap('StoreCodeNameMap', Code, 'Name not found') AS Transactions_Store_Name

FROM Transactions.Source;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

5 Replies
nagaiank
Valued Contributor III

Matching fields to data in different table

If you load the Excel worksheet having the branch number, name and other attributes in one table, it will solve your problem and you can remove the 'if' statements to map [Branch No] to [Branch Name].

gerhardl
Contributor II

Matching fields to data in different table

I must be doing something wrong because I get the "your document contains loops" message when I load the script this way.

nagaiank
Valued Contributor III

Matching fields to data in different table

If you post your qvw file with sample data or at least your script, the forum will be able to help.

Re: Matching fields to data in different table

Hello Gerhard,

It seems to me an example of one of the uses for ApplyMap() function, if I got you right

StoreCodeNameMap: // one map will do for all tables

MAPPING LOAD Code,

     Name

FROM File.xls;

AccountExtract:

LOAD *,

     ApplyMap('StoreCodeNameMap', Code, 'Name not found') AS Extract_Store_Name

     // "Name not found" literal above will be returned should no code match the mapping table

     // Making easier to identify where the problem is

FROM Extracts.Source;

Transactions:

LOAD *,

     ApplyMap('StoreCodeNameMap', Code, 'Name not found') AS Transactions_Store_Name

FROM Transactions.Source;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

gerhardl
Contributor II

Re: Matching fields to data in different table

Works like a charm! Beautiful, thank you.

Community Browser