topic Re: Creating a Relationship between two sets of data in QlikView

Creating a Relationship between two sets of data

Thu, 27 Sep 2012 10:58:03 GMT

Hi,

I have ran two separate SQL commands to get data from a table called OPENCALL and another called FIXCODE.

In my OPENCALL table I have a field called fixcode which shows data as such FLT0-CRPR-RCL0-SSWO.

The code has a certain value to it, in the above case FLT0 means Fault, CRPR means Corporate Software - RCL0 means Oracle and SSWO means Password Reset. As you can also see they are set a certain Levels, ie: FLT0 is Level 1, CRPR is level 2 etc.

In the second table called fixcode, I have a field called code, which shows all of the code, regardless of level and in the same table there is another field called descx which gives it the value.

I have created 4 listboxes that hold the fixcode from my OPENCALL table. I have separated it into 4 different list boxes, so in the above example, we have a list box called: Level 1 which holds FLT0, Level 2 which holds CRPR, Level 3 which holds RCL0 and Level 4, which holds SSWO.

I then created 2 other listboxes from my FIXCODE table. All the hold is the code and description.

What I am looking to achieve is creating a relationship between the two. As the FIXCODE table holds the relationship between the code and the description, my OPENCALL table only holds the code per call raised. (So if a call if raise on our helpdesk it is given the code above).

I want to be able to qlikview to look at the code in the OPENCALL table, match it to the code in the FIXCODE table and then bring through the description from the FIXCODE Table.

Is this possible?

Re: Creating a Relationship between two sets of data

Gysbert_Wassenaar — Thu, 27 Sep 2012 11:09:32 GMT

It's probably easiest to use FIXCODE as a mapping table and use applymap to include the code description in the load script.

Re: Creating a Relationship between two sets of data

Thu, 27 Sep 2012 11:15:26 GMT

Hi,

The problem is is that the FIXCODE data may change as it is not static. Therefore SSWO may one day to change PSSW. If it is changed on the application side, it will be amended in the FIXCODE table, but not the OPENCALL table which will stay the same.

Re: Creating a Relationship between two sets of data

Gysbert_Wassenaar — Thu, 27 Sep 2012 11:24:55 GMT

I don't see the problem If you change OSSW to PSSW then the records in OPENCALL that still contain OSSW won't be matched with PSSW. Unless you add PSSW to FIXECODE so that it contains both codes. In that case records in OPENCALL that have the old OSSW value will be matched with the description for OSSW and records with the new PSSW will be matched with the description for PSSW. In no case will OSSW records end up with the description of PSSW.

Re: Creating a Relationship between two sets of data

Thu, 27 Sep 2012 11:30:39 GMT

Thanks for your help on this one. Ill give the mapping table a try and see if I can get the desired results.