14 Replies Latest reply: Apr 26, 2016 5:31 AM by Mohit Kumar RSS

    Apply Map not working

    Mohit Kumar

      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

        • Re: Apply Map not working
          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$);

          • Re: Apply Map not working
            Stefan Wühl

             

            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

              • Re: Apply Map not working
                Mohit Kumar

                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

                  • Re: Apply Map not working
                    Silambarasan P

                    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.

                    • Re: Apply Map not working
                      Stefan Wühl

                      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.

                        • Re: Apply Map not working
                          Stefan Wühl

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