Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Joining Fields for Set Analysis

Table one holds information on Students, Program, School and College,

Table two the target table holds target records for NewHome/EU, NewOverseas, AllHome/EU, AllOverseas, NewHome/EU and NewOveseas by College, School and Program.

In the script they are joined by Program, however we need to be able to join both in a number of ways when doing set analysis

For example

When the user selects the Home/EU filter (a field coming from the main table) the target number should display the All_Home/EU figures (a field coming from the target table). 

When the user selects the New filter (a field coming from the main table) the target number should display New_Home/EU + New_Overseas (Coming from the target table) figures. and so on……

.

I have in part got this to work, however when I look at the chart the points for the actual from the main table and targets do not match on the charts.  Any advice gratefully appreciated.

Regards

Maria

1 Solution

Accepted Solutions
jason_michaelid
Honored Contributor II

Re: Joining Fields for Set Analysis

Hmmm, it's taken a while to unpick your script a bit and I think you need to look carefully at your data model. At this stage I'll try some generic advice as it's difficult to do more without an example of the source data...

1. (not really related to the question) Consider using ApplyMap() with source or inline mapping tables instead of your IF() statements. Much more expandable.

2. Look at using a composite key to join the tables, something like PROGRAM_CODE & Residency & 'New/Continuing' AS %KEY.  Obviously you will need to substitute Residency and 'New/Continuing' for the logic that defines these fields. Essentially, you need to build a key that joins all the bits of data you want to compare between the two tables. You will then need to remove PROGRAM_CODE from one of the tables - ensure you leave it in the one that is guaranteed to have all possible values.

Hope this helps a bit - if you post some source data we can help further.

Jason

6 Replies
Highlighted
jason_michaelid
Honored Contributor II

Joining Fields for Set Analysis

Can you post what you have so far, and some example data?  Much easier to help then...

Not applicable

Re: Joining Fields for Set Analysis

See attached

Regards

Maria

jason_michaelid
Honored Contributor II

Re: Joining Fields for Set Analysis

Hmmm, it's taken a while to unpick your script a bit and I think you need to look carefully at your data model. At this stage I'll try some generic advice as it's difficult to do more without an example of the source data...

1. (not really related to the question) Consider using ApplyMap() with source or inline mapping tables instead of your IF() statements. Much more expandable.

2. Look at using a composite key to join the tables, something like PROGRAM_CODE & Residency & 'New/Continuing' AS %KEY.  Obviously you will need to substitute Residency and 'New/Continuing' for the logic that defines these fields. Essentially, you need to build a key that joins all the bits of data you want to compare between the two tables. You will then need to remove PROGRAM_CODE from one of the tables - ensure you leave it in the one that is guaranteed to have all possible values.

Hope this helps a bit - if you post some source data we can help further.

Jason

Not applicable

Re: Joining Fields for Set Analysis

Hello Jason,

Thanks for getting back to me and thanks for the advice on ApplyMap I will have a bash at that this afternoon.

I have attached the source data and the latest version of the QVW file I have been working on. This is not the actual data as we will use as we are still building the data model and waiting for the server to be installed. So I put together this test data to enable me to start practicing before we get started. However these are some of the issues we will encounter.

                 Table one is the Conversion_Reg_Data file

               Table 2 is the 20120221PlannedNumbersTemplateSIDV1 file   this table holds the target numbers.

The biggest problem for me is that I have no control on the format of the the target numbers in the planning template. I had thought about using a composite key, but I can’t work out how I will join these fields as the key is dependent upon the selection of conditions - Region Home/EU or Overseas and New or ALL with the main data!

Regards

Maria

jason_michaelid
Honored Contributor II

Re: Joining Fields for Set Analysis

Hi Maria,

I will try and look at this later for you as I have to finish something else right now.  However, after a first glance at your data you may want to look into QlikView's Crosstable function to transform some of your data in a more appropriate format.

Cheers,

Jason

Not applicable

Re: Joining Fields for Set Analysis

There is no rush with this as I am away on a course for a few days and wont get a chance to look at this again till Monday.

Regards

Maria