Mapping Tables and linking fields

[#1]

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

[cesaraccardi]

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;

[#3]

Hi

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};

[#4]

Hi

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};

[cesaraccardi]

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