Apply Map not working

[#1]

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

[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]

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

[#4]

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

[#5]

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]

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';

[#8]

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

[arasantorule]

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]

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.