Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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$);

View solution in original post

14 Replies
sunny_talwar

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$);

swuehl
MVP
MVP

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
Author

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
Author

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

swuehl
MVP
MVP

Could you post a small sample QVW?

sunny_talwar

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
Author

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

arasantorule
Creator III
Creator III

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.

swuehl
MVP
MVP

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.