SQL Select and Mapping Load

Wed, 08 Jul 2009 04:54:53 GMT

So I am attempting to create a 'mapping load' inline to change the values of month numbers (1,2..) into month names (Jan, Feb..). I have other needs to but I would like to get this working first. I have not found any examples that make use of a mapping table when bringing in data from a 'SQL SELECT.' I have attempted multiply ways to bring in the 'map' as you can see by the comment fields and they have all failed. Is their a way to use the 'load' functions (MonthName for example) with a SQL table? How would I make this mapping table work for a SQL select? Is their an easier way to do this?

Thanks for your help and any questions you may have.

//mapping table

MonthMap:
MAPPING Load * Inline [
Month, MonthName
1, January
2, February
3, March
4, April
5, May
6, June
7, July
8, August
9, September
10, October
11, November
12, December
];

//SQL select

SelectP21:
SQL SELECT
total_amount,
customer_id,
commission_cost AS Commission,
customer_name,
Month(invoice_date) AS InvoiceMonth,
//MonthName(invoice_date) AS InvoiceMonth_Name,
//Applymap('MonthMap', [MonthName]) AS [InvoiceMonth_Name],
//daul(applymap('MonthMap', MonthName),MonthName) AS InvoiceMonth_Name,
//PICK(invoice_date,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') AS InvoiceMonth_Name,
Year(invoice_date) AS InvoiceYear,
first_name,
last_name
FROM CommerceCenter.dbo.p21_sales_analysis_view;

SQL Select and Mapping Load

Anonymous — Wed, 08 Jul 2009 07:05:07 GMT

Jacob,
SQL doesn't know QlikView functions. You can use these functions only in LOAD. You can use preceeding load, like this:


table1:
LOAD
 field1,
 applymap('MapName', field2) as NewField2,
 ...
;
SQL SELECT
 field1,
 field2,
 ...
FROM ...;

topic SQL Select and Mapping Load in QlikView

SQL Select and Mapping Load

SQL Select and Mapping Load