Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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};
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};
Hi, you must put the name of table with 'MAP_Lookup'