2 Replies Latest reply: Dec 7, 2016 3:39 AM by Massimo Grossi RSS

    Problems using ApplyMap after Count

    Giles Walker

      Hi Community,

       

      Spent a day on this and getting nowhere. (I'm using Qlik Sense by the way, and am only 6 weeks into my Qlik journey = newbie!).

       

      Here is the task: I need to take a table, and count the number of orders.  Then I need to map that count of orders to a different 'main' table, using ApplyMap.  I can't get it to work.  I have used ApplyMap successfully before, when I was merely extracting data from pre-defined tables, but with this I am having to do some calcs before using ApplyMap.  I'm doing something wrong.

       

      Here is what I have done:

       

      <<This is the base table load put into its own section in the data load editor>>

       

      orders:

      LOAD warehouse_id,

                wave_seq_num,

                order_id,

                order_status

                Where order_status < '100';

       

      [orders]:

      SELECT "warehouse_id",

            "wave_seq_num",

            "order_id",

            "order_status"

      FROM "ph_prod".dbxx.orders;

       

      <<Then to count the order_id's I simply carry on the above script straight away with the below script>>

       

      OrderCount:

      LOAD  wave_seq_num,

                 Sum(If(order_id, '0','1')) as OrderCount

                 Resident orders

                 Group By warehouse_id, wave_seq_num;

       

      <<The purpose of this OrderCount table is to give me the total number of orders per unique 'warehouse_id' and 'wave_seq_num' combination.  However as you can see I have had to limit the LOAD to exclude 'warehouse_id' due to synthetic keys that popped up which I assume is bad. I'm not sure if what I have here in the OrderCount table will give me that count by unique 'warehouse_id' and 'wave_seq_num' combination of whether its just giving me the count by 'wave_seq_num'??  The base data coming back from the server doesn't give me the data I need to check this at this current time, but into the future that may change, so this needs to be scripted correctly.  Can anyone clarify this please?>>


      <<After that I need to map this OrderCount data to my 'main' table. The script I use (inputted straight after I finish the OrderCount table) looks like this:>>

       

      OrderCountMapping:

      Mapping LOAD  warehouse_id & wave_seq_num as UniqueKey,

                              Sum(If(order_id, '0','1')) as OrderCount;

       

       

      <<Then in my main table "waves" (which is in a subsequent new section of its own in the data load editor, occurring after the above so the sequencing is correct), the corresponding ApplyMap (in pink below) is set up to hopefully pull through the data.>>

       

      waves:

      LOAD  warehouse_id,

                description,

                wave_seq_num,

                warehouse_id & wave_seq_num as UniqueKey,

                ApplyMap('OrderCountMapping',warehouse_id & wave_seq_num) as OrderCount,

                Date(BP_avail_date,'D/MM/YYYY hh:mm:ss TT') as BP_avail_datetime,

                Date(BP_start_date,'D/MM/YYYY hh:mm:ss TT') as BP_start_datetime,

                Date(BP_finish_date,'D/MM/YYYY hh:mm:ss TT') as BP_finish_datetime,

                BP_start_usr_id,

                Date(PP_start_date,'D/MM/YYYY hh:mm:ss TT') as PP_start_datetime,

                Date(PP_complete_date,'D/MM/YYYY hh:mm:ss TT') as PP_complete_datetime,

                wave_comments,

                wave_closed_flag,

                restocks_released,

                wave_type,

                direct_pick_flag,

                PP_start_usr_id

                Where wave_type <> 'FAST' and IsNull(PP_complete_date);

      [waves]:

      SELECT.....................

      FROM "ph_prod".dbxx.waves;

       

      <<Problem is that Qlik errors at this point, and says "ApplyMap Error - map_id not found".  I am sure I am making a rookie error in here somewhere - if anyone can help me I would really appreciate it.>>

        • Re: Problems using ApplyMap after Count
          Liron Baram

          hi

          i would have made this change

          instead of this two steps ,

          OrderCount:

          LOAD  wave_seq_num,

                     Sum(If(order_id, '0','1')) as OrderCount

                     Resident orders

                     Group By warehouse_id, wave_seq_num;

           

           

          OrderCountMapping:

          Mapping LOAD  warehouse_id & wave_seq_num as UniqueKey,

                                  Sum(If(order_id, '0','1')) as OrderCount;

           

          use this one :

          OrderCountMapping:

          Mapping LOAD  warehouse_id & wave_seq_num as UniqueKey,

                                  Sum(If(order_id, '0','1')) as OrderCount

            Resident orders

                     Group By warehouse_id, wave_seq_num;

           

          because you must include the wharehouse_id in order to create the right mapping ;

          • Re: Problems using ApplyMap after Count
            Massimo Grossi

            1)

            Another option is to remove the mapping load (OrderCount and OrderCountMapping) and to add a left join after the load of waves table; the join is by warehouse_id and wave_seq_num, the unique key of waves

             

            Left join (waves)

            LOAD

                warehouse_id,

                wave_seq_num,

                //Sum(If(order_id, '0','1')) as OrderCount

                count(order_id) as OrderCount

            Resident orders

            Group By warehouse_id, wave_seq_num;



            2)

            Maybe you don't need an OrderCount field in the script. The waves and orders tables are associated by 2 fields and you can count the order in the user interface

             

             

            3)

            due to synthetic keys that popped up which I assume is bad.

            If you have time

            Should We Stop Worrying and Love the Synthetic ... | Qlik Community