topic Mapping Tables and linking fields in QlikView

Mapping Tables and linking fields

Wed, 16 May 2012 13:25:12 GMT

I am new to qlikview and looking for some help or a possible solution to a query I have encountered when linking Qlikview with our internal Support System.

There is 1x main FACT table (Support_Hd) which contains a number of fields which are populated with internal references (values) which are

Generated when a log is raised or edited, these values are based on entries in a Lookup table.

// Load FACT Table

Support_Hd:
LOAD
`log_ref`                          AS STHD_LogReference,               (Promary key field)
//Linking field - Used to link to the Support_Dt table
`log_ref`                          AS KEY_LogReference,               (Int Ref Value)
`log_stat`                        AS KEY_LogStatus,                    (Int Ref Value)
`Cont_Meth`                    AS KEY_ContactMethod,             (Int Ref Value)

`Prod_lkp` AS KEY_ProductLookup, (Int Ref Value)

`Dept_lkp` AS KEY_DepartmentLookup (Int Ref Value)

;
SQL SELECT *
FROM `Support_Hd` where log_dt > {^2012-01-01};

I am currently having to link the Lookup table 4x times for each lookup and this doesn’t seem the best or most optimised solution.

// Load LOOKUP Table

Lookup:
LOAD
`decode_ref` AS KEY_LogStatus, (Int Ref Value)
code,
description,
tag,
;
SQL SELECT *
FROM lookup;

I have attempt to use a Mapping Load command to apply the description to the main FACT table (Support_Hd) but the script

Fails due to the two comparison fields are not the same even though they contain the same reference values.

Mapping Code below:

MAP_Lookup:
LOAD
//`decode_ref`,
applymap('MapName',`decode_ref`) AS log_stat,
`description
;
SQL SELECT *
FROM chsysdec;

Applymap(MAP_Lookup,`log_stat`) AS STHD_LogStatus,

Any help or solution would be much appreciated

Mapping Tables and linking fields

cesaraccardi — Wed, 16 May 2012 18:32:15 GMT

Hi,

To use the mapping functionality you must first create your map table ( It must have only 2 fields and the keyword MAPPING must preceed the load statement), after that you'll have this table at memory available for the next loads. Example:

MAP_TABLE:
MAPPING LOAD
* INLINE[

from,to
a,Active

i,Inactive
];

LOAD
X,
APPLYMAP('MAP_TABLE',Stats) as Stats
FROM
TABLE;

Mapping Tables and linking fields

Thu, 17 May 2012 08:04:47 GMT

Thanks for your response.

I have generated the Mapping Table as suggested and the script still fails and I still believe this is because the fields are named differently in the two source tables even though they contain the same data and I was hoping there could be away around this.

// Mapping Table

MAP_Lookup:

MAPPING LOAD

'decode_ref' as log_stat,

description

;

Select *

FROM LOOKUP

where Code='LOST'

// FACT Table

SUPPORT_HD:

LOAD

'Log_Ref',

// 'log_stat',

Applymap(MAP_Lookup,log_stat) as LogStatus

;

SQL SELECT *

FROM 'Support_Hd' where log_dt > {^2012-01-01};

The system fails stating the following error:

Field not found - <MAP_Lookup>

SQL SELECT *

FROM 'Support_Hd' where log_dt > {^2012-01-01};

Re: Mapping Tables and linking fields

Thu, 17 May 2012 08:06:23 GMT

Thanks for your response.

// Mapping Table

MAP_Lookup:

MAPPING LOAD

'decode_ref' as log_stat,

description

;

Select *

FROM LOOKUP

where Code='LOST'

// FACT Table

SUPPORT_HD:

LOAD

'Log_Ref',

// 'log_stat',

Applymap(MAP_Lookup,log_stat) as LogStatus

;

SQL SELECT *

FROM 'Support_Hd' where log_dt > {^2012-01-01};

The system fails stating the following error:

Field not found - <MAP_Lookup>

SQL SELECT *

FROM 'Support_Hd' where log_dt > {^2012-01-01};

Mapping Tables and linking fields

cesaraccardi — Tue, 22 May 2012 21:55:13 GMT

Hi, you must put the name of table with 'MAP_Lookup'