Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
gileswalker
Contributor

Problems using ApplyMap after Count

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.>>

Tags (2)
2 Replies
Partner
Partner

Re: Problems using ApplyMap after Count

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 ;

MVP
MVP

Re: Problems using ApplyMap after Count

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