5 Replies Latest reply: Jun 28, 2011 6:43 AM by Gerhard Laubscher RSS

    Matching fields to data in different table

    Gerhard Laubscher

      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