Help please with linking data in two tables to make a new QVD

[#1]

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.

[geert_gelade · Accepted solution]

Please check the attached qvw if this is the solution you want.

This is based on a generic load and a loop over all tables of the generic load to compose the final table.

[#3]

Wow Geert, what a lot of work - thanks very much for taking a look.  At first view it looks perfect, but I need to build your formula/processes into my rather large data files which may take a little while to get right as I'm still no expert at this.  If nothing else it has shown there was no quick/easy fix which was what I thought, but I'm really hopeful I can get it working and will let you know. Cheers.

[#4]

Confirmed as working perfectly - thanks very much