1

I am trying to load a field that has numbers from 1 to 12. But I want the equivalent month name to be loaded into Qlik View. Like Instead of 1 I want the cell to contain Jan and instead of 2 I want it to be Feb etc.,Everywhere people are suggesting to make use of the set variable called MonthNames. When I load the table into qlik view I see there is set MonthNames="jan;feb;mar;apr;..' variable that could be used to assign corresponding month to the field. but I am not able to find an example of how to use it. Can anyone help please?

My field name is "Mon". I am not sure how to use this in my load statement in edit script.

Pal
  • 173
  • 2
  • 16

1 Answers1

3

For such cases you can use the Mappng Load functionality. You can think of Mapping load as a lookup function. When called during data load then for each value loaded the mapping will try to find a match in the Mapping table. If no match is found it will return the original value.

Mapping tables are not part of the data model and they dont need to be dropped

In the example below the mapping table is called Months and it contains the numbers from 1 to 12 and against each number there is a string value that should be returned. The mapping is called using the ApplyMap function that accepts 2-3 parameters:

  • mapping name - the name of the mapping table (the name should be in quotes)
  • field name - the filed on which the mapping should be applied
  • default value (optional) - if not specified when match is not found the original value is returned. This can be overwritten with this parameter. For example: ApplyMap('MyMappingTable', MyField, 'No match found')

Script:

Months:
Mapping
Load * Inline [
  Old, New
  1  , Jan
  2  , Feb
  3  , Mar
  4  , Apr
  5  , May
  6  , Jun
  7  , Jul
  8  , Aug
  9  , Sep
  10 , Oct
  11 , Nov
  12 , Dec
];


Data:
Load
  MyFeld, // this will contain the original values
  ApplyMap('Months', MyField) as MyField_Months // this will contain the month names values
From
  MyData.qvd (qvd)
;

Update:

You can use the set variable MonthNames to generate the mapping table. The way to do this is to split the content of the variable on ; and produce a row for each element and associate an id for it. Qlik have a nice function for splitting string into multiple rows - SubField()

SubField() accepts two parameters (and one optional)

  • Field name - valid field name that contains the string value
  • Delimiter - string on which the string should be split
  • Possition (optional) - the function can split the string and return the value on position N. Specifying this parameter will return only one value from the original string

In this case the string contains the month names separated by ; - 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

When SubField() is called with this string the result table will be:

Jan
Feb
Mar
Apr
...

Updated script:

Months:
Mapping // comment this row to view the "real" table
// Use SubField function to create N number of rows
// by separating the variable on ';'
// Use RowNo() function to generate and Id (1 ... 12)
// For each row
Load
  RowNo()                        as Id,
  SubField(MonthNames_Temp, ';') as MonthNames
;
// Get the content of the MonthNames variable as a string
// and generate one-row table
Load
  '$(MonthNames)' as MonthNames_Temp
AutoGenerate(1)
;
Stefan Stoichev
  • 4,615
  • 3
  • 31
  • 51
  • Thank you @stefan. I came across similar mapping technique but I really want to know how to use that "set MonthNames='Jan;Feb,..' option within edit script. It is really frustrating not able to find a single example/instruction of how to use that variable which is sitting right infront of your eyes. Do you know anything about it? – Pal Jul 08 '19 at 05:18
  • @Pal updated the answer to use the `MonthNames` variable as a source for the mapping table. Just bear in mind that values in these variables are based on your machine local settings. – Stefan Stoichev Jul 08 '19 at 07:22