Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1 Solution

Accepted Solutions
geert_gelade
Creator
Creator

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.

View solution in original post

3 Replies
geert_gelade
Creator
Creator

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.

Not applicable
Author

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.

Not applicable
Author

Confirmed as working perfectly - thanks very much