Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Apply Map not working

Hi All,

I have been trying to use ApplyMap in QlikSense and I followed many examples. But it's not working properly and I'm not getting the desired output.

Following is the script that I'm using:

MonthMap:

Mapping LOAD * INLINE

[    MonthName, MonthNum      

     Jan, 1   

     Feb, 2   

     Mar, 3

     Apr, 4   

     May, 5   

     Jun, 6   

     Jul, 7   

     Aug, 8   

     Sep, 9   

     Oct, 10   

     Nov, 11   

     Dec, 12

];

Orders:

LOAD

    OrderID,

    OrderDate,

    Month(OrderDate) as "MonthName",

    CustomerID,

    EmployeeID,

    ShipperID,

    ProductID,

    Sales,

    "COS",

    GP,

    Quantity,

    Discount,

    Freight

FROM [lib://Data/Orders - Copy.xls]

(biff, embedded labels, table is Orders$);

Temp:

Load Distinct *,

ApplyMap('MonthMap',MonthName,'No Month') as Month_Num

Resident Orders;

Drop table Orders;

Any help regarding this will be highly appreciated.

Best Regards,

Mohit Kumar

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Apply Map not working

You may need to match the text() part of your MonthName to the mapping key field:

MonthMap:

MAPPING

LOAD * INLINE

[    MonthName, MonthNum   

    Jan, 1

    Feb, 2

    Mar, 3

    Apr, 4

    May, 5

    Jun, 6

    Jul, 7

    Aug, 8

    Sep, 9

    Oct, 10

    Nov, 11

    Dec, 12

];

Orders:

LOAD

    OrderID,

    OrderDate,

     ApplyMap('MonthMap', Text(Month(OrderDate)), 'not found') as MonthNum,

    Month(OrderDate) as "MonthName",

    CustomerID,

    EmployeeID,

    ShipperID,

    ProductID,

    Sales,

    "COS",

    GP,

    Quantity,

    Discount,

    Freight

FROM [lib://Data/Orders - Copy.xls]

(biff, embedded labels, table is Orders$);

14 Replies
MVP
MVP

Re: Apply Map not working

I don't think you need ApplyMap here, can you try this:

Orders:

LOAD

    OrderID,

    OrderDate,

    Month(OrderDate) as "MonthName",

     Num(Month(OrderDate)) as Month_Num,

    CustomerID,

    EmployeeID,

    ShipperID,

    ProductID,

    Sales,

    "COS",

    GP,

    Quantity,

    Discount,

    Freight

FROM [lib://Data/Orders - Copy.xls]

(biff, embedded labels, table is Orders$);

MVP
MVP

Re: Apply Map not working

I have been trying to use ApplyMap in QlikSense and I followed many examples. But it's not working properly and I'm not getting the desired output.

Please, don't stop here. It's important to know what you get from your current script, and what you expect to get.

I can only guess that your current script is not working as expected, because your OrderDate is not correctly interpreted as date, and hence the QlikSense function Month() is not returning a value, that can be mapped using your mapping table.

So first step (also to make Sunny's script work if it doesn't work from the start) is to check your OrderDate values:

Why don’t my dates work?

Either use acorrect default format code in your script or use Date#() function to interprete your OrderDate values.

SET DateFormat = 'MM/DD/YYYY'; //adapt to the appropriate format code

Orders:

LOAD

    OrderID,

    OrderDate,

    Month(OrderDate) as "MonthName",

...



or


Orders:

LOAD

    OrderID,

    Date#(OrderDate,'MM/DD/YYYY') as OrderDate,

    Month(Date#(OrderDate,'MM/DD/YYYY')) as "MonthName",

....

But since Month() already returns a dual value with a numeric part, you don't really your mapping nor to create another field for the numeric representation (well, at least in most scenarios I can think of).

Data Types in QlikView

Not applicable

Re: Apply Map not working

Hi Swuehl,


Thanks for the help.

I tried the above given solution but still, it doesn't work for my script. I tried other examples(without the dates) and ApplyMap function is working fine. But I'm still stuck on this and want to know what's wrong with the script.

Best Regards,

Mohit Kumar

Not applicable

Re: Apply Map not working

Hi Sunny,

Thank you for your help.

I know I don't need to use ApplyMap here or create a new field to use Month Number. But I want to know what is wrong in the script that I'm using. I followed some other examples (without the date field) and ApplyMap is working fine. Also, I don't see any error in this script and logically, this should work.

Anyways, I'll continue looking for the error.



Best Regards,

Mohit Kumar

MVP
MVP

Re: Apply Map not working

Could you post a small sample QVW?

MVP
MVP

Re: Apply Map not working

Can you check if you have this SET statement in your environment variable?

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Not applicable

Re: Apply Map not working

Yes, this set statement is present while defining the Environment Variables.

arasantorule
Contributor III

Re: Apply Map not working

Hi Mohit,

Can you check if the field "OrderDate" is correctly formatted to date?


If not, you can use the date function to convert it to month.


     Example : month(date#(OrderDate, ' Your Format like MM/DD/YYYY hh:mm:ss')) as month


Thanks.

MVP
MVP

Re: Apply Map not working

And to check why your mapping does not work, you could try to link the tables instead of map:

MonthMap:

Mapping //Remove the Mapping LOAD prefix

LOAD * INLINE

[    MonthName, MonthNum    

    Jan, 1

    Feb, 2

    Mar, 3

    Apr, 4

    May, 5

    Jun, 6

    Jul, 7

    Aug, 8

    Sep, 9

    Oct, 10

    Nov, 11

    Dec, 12

];

Orders:

LOAD

    OrderID,

    OrderDate,

    Month(OrderDate) as "MonthName",

    CustomerID,

    EmployeeID,

    ShipperID,

    ProductID,

    Sales,

    "COS",

    GP,

    Quantity,

    Discount,

    Freight

FROM [lib://Data/Orders - Copy.xls]

(biff, embedded labels, table is Orders$);

Now create a table with dimensions OrderDate, MonthName and MonthNum and check if you get correct relations between field values. If not, you should see the difference between MonthName values.