topic Joining Fields for Set Analysis in QlikView

Joining Fields for Set Analysis

Tue, 28 Feb 2012 11:00:06 GMT

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

Joining Fields for Set Analysis

Jason_Michaelides — Tue, 28 Feb 2012 11:16:09 GMT

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

Re: Joining Fields for Set Analysis

Tue, 28 Feb 2012 11:36:51 GMT

See attached

Regards

Maria

Re: Joining Fields for Set Analysis

Jason_Michaelides — Tue, 28 Feb 2012 14:09:40 GMT

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

Re: Joining Fields for Set Analysis

Tue, 28 Feb 2012 15:12:12 GMT

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

Re: Joining Fields for Set Analysis

Jason_Michaelides — Tue, 28 Feb 2012 15:59:43 GMT

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

Re: Joining Fields for Set Analysis

Wed, 29 Feb 2012 09:29:08 GMT

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