I have been using Qlikview for a while now and am writing my own script including using the ApplyMap function quite a bit, but can't figure out where to start with this - any help/advice is appreciated.
I have Table 1 below with a unique code for each date and empty columns for Reasons & Counts. Not all codes exist against all dates
TABLE 1 | | | | | | | |
Code | Date | Reason 1 | Count 1 | Reason 2 | Count 2 | Reason 3 | Count 3 |
ABC123 | 01/09/2014 | | | | | | |
ABC124 | 01/09/2014 | | | | | | |
ABC125 | 01/09/2014 | | | | | | |
ABC123 | 02/09/2014 | | | | | | |
ABC124 | 02/09/2014 | | | | | | |
ABC125 | 02/09/2014 | | | | | | |
Table 2 has a list of multiple codes and dates and different reason and codes against each pair of codes and dates. There are up to 3 identical codes listed against each date, but always with a different reason and maybe the same count.
TABLE 2 |
Code | Date | Reason | Count |
ABC123 | 01/09/2014 | A | 1 |
ABC123 | 01/09/2014 | B | 2 |
ABC123 | 01/09/2014 | C | 3 |
ABC124 | 02/09/2014 | D | 4 |
ABC124 | 02/09/2014 | D | 5 |
ABC125 | 01/09/2014 | C | 4 |
ABC125 | 02/09/2014 | B | 3 |
ABC125 | 02/09/2014 | A | 2 |
I need to extract the Reason & Count for each Code and Date pairing in Table 2 and include them as part of Table 1, adding the the Reason & Count columns sequentially if the first reason & count column is already occupied for each Code & Date pairing so that Table 1 ends up like this
TABLE 1 - NEW QVD | |
Code | Date | Reason 1 | Count 1 | Reason 2 | Count 2 | Reason 3 | Count 3 |
ABC123 | 01/09/2014 | A | 1 | B | 2 | C | 3 |
ABC124 | 01/09/2014 | |
ABC125 | 01/09/2014 | C | 4 | |
ABC123 | 02/09/2014 | |
ABC124 | 02/09/2014 | D | 5 | |
ABC125 | 02/09/2014 | B | 3 | A | 2 | | |
Effectively I want to do a 'Lookup' of a Date-Code join of the multiple entries in TABLE 2 and insert the relevant Reason & Count data into TABLE 1 where the lookup is matched, but sequentially insert that data to Reason 2 or Reason 3 columns where a previous match/entry has already been made in Reason1/Count1. As a preference I'd like the sequencing to put the highest 'Count' from any matches into order - Count 1 being the highest etc., but this is not overly important to achieve. I would ideally like to achieve this combining of data at the script stage so that I am producing a new QVD from Table 1.
