topic Help with Mapping Table and Apply Map in QlikView

Help with Mapping Table and Apply Map

Thu, 27 Sep 2012 14:27:51 GMT

Hi,

I am relatively new to Qlikview but have come across a stumbling block. I am trying to merge 2 separate SQL tables into one and create a relationship between a field called fixcode in TABLE1 (OPENCALL) and a field called code in TABLE2 (FIXCODE).

I have been advised to use an APPLYMAP function in my LOAD script, but I have never done one before.

I have attached a copy of the details in my load script at the moment in text pad format.

In my OPENCALL table the code is in a single string separated by hyphens. It is stored in a field called fixcode. Therefore my code looks likes this:

FLT0-CRPR-RRCL-SSWO

RQST-CORP-RCC1-NWSP

etc....

In my FIXCODE table all of the codes are stored in a field called code and each 4 digit code is entered into a row. Theres is also another field called descx which sets a value to the code. In ths Table it looks like this:

Code	descx
FLT0	Fault
RQST	Request
RCC1	Oracle
SSWO	Password Reset

I will be amending my script to amend the OPENCALL table so that I can put each code into seprate columns. However what I then want to do is match the code from my OPENCALL table and match it with the FIXCODE Table so the the following will show:

FLT0-CRPR-RRCL-SSWO

Fault-Corporate Software-Oracle-Password Reset

I have no idea how to even start the Mapping table. I was wondering if someone could help me figure out how to do this.

Regards,

Jon Ditchfield

Re: Help with Mapping Table and Apply Map

Gysbert_Wassenaar — Thu, 27 Sep 2012 14:50:31 GMT

maybe something like this:

Mapping loadFixcodes:

load

Code,

Descx

from Table2;

Opencall:

load *,

subfield(fixcode,'-',1) as level1,

subfield(fixcode,'-',2) as level2,

subfield(fixcode,'-',3) as level3,

subfield(fixcode,'-',4) as level4,

applymap('Fixcodes',subfield(fixcode,'-',1)) as level1desc,

applymap('Fixcodes',subfield(fixcode,'-',2)) as level2desc,

applymap('Fixcodes',subfield(fixcode,'-',3)) as level3desc,

applymap('Fixcodes',subfield(fixcode,'-',4)) as level4desc,

applymap('Fixcodes',subfield(fixcode,'-',1)) & '-' &

applymap('Fixcodes',subfield(fixcode,'-',2)) & '-' &

applymap('Fixcodes',subfield(fixcode,'-',3)) & '-' &

applymap('Fixcodes',subfield(fixcode,'-',4)) as fulldescx

from Table1;

Re: Help with Mapping Table and Apply Map

Thu, 27 Sep 2012 15:01:14 GMT

Hi,

I am getting the following Script Error:

Cannot open file c:\Documents and Setting\ditcjz\Desktop\opencall

Re: Help with Mapping Table and Apply Map

Fri, 28 Sep 2012 09:39:21 GMT

Hi,

I have a connection to the DB, I had to amend my load script. However now when I amend my Load script, a message occurs saying no such field fixcode.

Attached is a copy of my load script.

Re: Help with Mapping Table and Apply Map

Gysbert_Wassenaar — Fri, 28 Sep 2012 09:49:23 GMT

The table fixcode from which you are trying to load doesn't have a field fixcode, but code. However, I think you're referencing the wrong table for the Opencall load and should load from your opencall table instead which does have a field named fixcode.

Re: Help with Mapping Table and Apply Map

Fri, 28 Sep 2012 10:08:43 GMT

Thank you very much for your help. It was more than what I was expecting to be honest and its made my report look alot better. Thank you again.