topic Adding a derived field on a table box in QlikView

Adding a derived field on a table box

Tue, 22 Feb 2011 11:56:40 GMT

Hi,

I'm reading a SQL Server table and I'm putting the query result on a table box. In my SQL table I have a numeric month field and I want to show the name of month (January, February, March etc.). How can I add a derived field of the numeric month field on the table box? Do I need specify it in the SQL command at script level?

Thanks for your suggests.

Adding a derived field on a table box

prieper — Tue, 22 Feb 2011 12:38:18 GMT

You may do it by a mapping, like:

MapMonth: MAPPING LOAD
*
INLINE
[M,Month
1, January
2, February
3, March
etc];
Data:
LOAD
*,
APPLYMAP('MapMonth', MonthNum);
SQL SELECT
MonthNum,
...
FROM ....;<pre>
Albeit I always would prefer to convert the monthnumber into a real date, as it eases lateron a lot in the datarelations.
HTH
Peter</body>

Adding a derived field on a table box

matt_crowther — Tue, 22 Feb 2011 12:39:19 GMT

Assuming you have the date in a traditional format style (eg: 01/01/2011) then add the field to the Table Box as normal, go to Properties > Number > chech the Overide Default button and set the format of the Date field to be 'MMM' that would return 'Jan' based on an actual value of '01/01/2011'.

Alternatively you can as you suggest amend the script or as I often do simple create a Straight Table as this gives you much more flexibility.

Hope that helps,

Matt - Visual Analytics Ltd