18 Replies Latest reply: Nov 30, 2017 11:14 AM by Jonnie Yacoub RSS

    Canonical calendar issue

    Jonnie Yacoub

      Hey Qlik Community,

       

      I have trouble making an canonical calendar. I have read every post i could find on this subject and still could't make one... As you can see below i have used a LEFT JOIN for my table lead & deal. I have a date field [lead_inserted] and a date field [date_inserted]. Now i need this calendar because i want an count for leads and deals. For the dimension i need the canonical date. Any idea on how to make the calendar with my script? help would be very very very appreciated! Below my script...

       

      LIB CONNECT TO 'lead (qlikcloud_qlikid_jonnieyacoub)';

      [lead]:

      Load

           [id] AS lead_id,

          DATE([inserted], 'DD/MM/YYYY') AS [lead_inserted],

      //    YEAR([inserted]) AS [lead_year],

      //    MONTH([inserted]) AS [lead_month],

      //    DAY([inserted]) AS [lead_day],

           [client] AS lead_client,

          [name_last] AS lead_name_last,

          [priority] AS lead_priority,

          [status_followup_date] AS lead_status_followup_date,

          [status_followup_time] AS lead_status_followup_time,

          [status_followup_description] AS lead_status_followup_description,

          ApplyMap('lead_sourceMap',[source], null()) AS source,

          TEXT('lead') AS lead_type,

          [owner_user] AS user_id;

      SQL SELECT

      (SELECT

               "id",

              "inserted",

               "client",

              "name_last",

              "priority",

              "status_followup_date",

              "status_followup_time",

              "status_followup_description",

              "source",

              "owner_user"

      FROM "root")

      FROM JSON (wrap on) "root";

      LIB CONNECT TO 'deal (qlikcloud_qlikid_jonnieyacoub)';

       

       

      [deal]:

      LEFT JOIN

      Load

           [id] AS deal_id,

          DATE([inserted], 'DD/MM/YYYY') AS [deal_inserted],

      //    YEAR([inserted]) AS [deal_year],

      //    MONTH([inserted]) AS [deal_month],

      //    DAY([inserted]) AS [deal_day],

          [lead] AS lead_id,

          [office] AS deal_office,

          [owner_user] AS deal_user_id,

          [value_cents] AS deal_value_cents,

          [status_added] AS deal_status_added,

          [status_converted] AS deal_status_converted,

          [status_invoiced] AS deal_status_invoiced,

          TEXT('deal') AS deal_type,

          [status_lost] AS deal_status_lost;

      SQL SELECT

      (SELECT

          "id",

              "inserted",

              "lead",

              "office",

              "owner_user",

              "value_cents",

              "status_added",

              "status_converted",

              "status_invoiced",

              "status_lost"

      FROM "root")

      FROM JSON (wrap on) "root";

       

       

      and my try for the canonical calendar:

       

       

      mapleaddatum:

      mapping LOAD

           lead_id, lead_inserted RESIDENT lead;

       

      mapdealdatum:

      mapping LOAD

           deal_id, deal_inserted RESIDENT lead;

       

      datebridge:

      LOAD

           lead_id, ApplyMap('mapleaddatum',lead_id,Null()) as canonicaldate, 'lead' as datetype,

           deal_id, ApplyMap('mapdealdatum',lead_id,Null()) as canonicaldate, 'deal' as datetype

        RESIDENT lead;

       

       

      exit script;

        • Re: Canonical calendar issue
          Robert Hutchings

          Hi

           

          Have you read this. Most queries are answered in the comments

           

          Canonical Date

          • Re: Canonical calendar issue
            Shraddha Gajare

            What is issue you are facing?

              • Re: Canonical calendar issue
                Jonnie Yacoub

                Hi Shraddha,

                 

                When i try to load the CanonicalCalendar it gives me an error "Field names must be unique within table". I have attached an screenshot of my data-model. As you can see in the table "lead" (left join with deal) i have two dates that i need an Canonical Calendar for...Schermafbeelding 2017-11-30 om 13.27.29.png

                  • Re: Canonical calendar issue
                    A.M. van Keep

                    Hi Jonnie,

                     

                    your problem is marked in red. you should rename / remove one of the fields:

                     

                    datebridge:

                    LOAD

                         lead_id, ApplyMap('mapleaddatum',lead_id,Null()) as canonicaldate, 'lead' as datetype,

                         deal_id, ApplyMap('mapdealdatum',lead_id,Null()) as canonicaldate, 'deal' as datetype

                      RESIDENT lead;

                      • Re: Canonical calendar issue
                        Jonnie Yacoub

                        Hi sir van Keep,

                         

                        i just tried it and i keep getting the same error... changed one of the datetype to datetypeone..

                         

                        mapleaddatum:

                        mapping LOAD

                             lead_id, lead_inserted RESIDENT lead;

                         

                        mapdealdatum:

                        mapping LOAD

                             deal_id, deal_inserted RESIDENT lead;

                         

                        datebridge:

                        LOAD

                             lead_id, ApplyMap('mapleaddatum',lead_id,Null()) as canonicaldate, 'lead' as datetype1,

                             deal_id, ApplyMap('mapdealdatum',lead_id,Null()) as canonicaldate, 'deal' as datetype

                          RESIDENT lead;

                        exit script;

                          • Re: Canonical calendar issue
                            A.M. van Keep

                            I missed another field:

                             

                            datebridge:

                            LOAD

                            lead_id,

                            ApplyMap('mapleaddatum',lead_id,Null()) as canonicaldate,

                            'lead' as datetype1,

                                    deal_id,

                                    ApplyMap('mapdealdatum',lead_id,Null()) as canonicaldate,

                                    'deal' as datetype

                              RESIDENT lead;

                            exit script;

                              • Re: Canonical calendar issue
                                Jonnie Yacoub

                                Hmm, yeah now it is working but i have a syntatic key in my model. Maybe you can help me more i explain you my situation. So i have the table LEAD and DEAL. The table lead contains the fields lead_priority and lead_source. For my dashboard i wanted to know how many leads became deals and how many deals where closed from one of the many sources. So i did a left join for those 2 tables and problem solved. But now i want to make a stacked bar chart with as measures count(lead) and count(deal) and for dimension i want to use lead_inserted and deal_inserted. so i think i need an canonical calendar for those 2 dates. I hope you understand this...?

                                  • Re: Canonical calendar issue
                                    A.M. van Keep

                                    I think I understand.

                                     

                                    Try to concatenate the tables zo they only exists of 2 fields (instead of 4) .

                                     

                                     

                                    datebridge:

                                    LOAD

                                    lead_id,

                                    ApplyMap('mapleaddatum',lead_id,Null()) as canonicaldate,

                                    'lead' as datetype

                                      RESIDENT lead;


                                     

                                    CONCATENATE (datebridge) LOAD

                                            deal_id,

                                            ApplyMap('mapdealdatum',lead_id,Null()) as canonicaldate,

                                            'deal' as datetype

                                      RESIDENT lead;

                                      • Re: Canonical calendar issue
                                        Jonnie Yacoub

                                        Im not quite sure witch tables you mean... i tried this and both options did not work...:

                                         

                                        calendarlead:

                                        LOAD

                                        lead_id,

                                            lead_inserted as canonicaldate

                                          RESIDENT lead;

                                         

                                        calendardeal:

                                        CONCATENATE

                                        LOAD

                                        deal_id as lead_id,

                                            deal_inserted as canonicaldate

                                          RESIDENT lead;

                                        exit script;

                                         

                                        and this:

                                         

                                        calendarlead:

                                        LOAD

                                        lead_id,

                                            ApplyMap('mapleaddatum',lead_id,Null()) as canonicaldate,

                                        'lead' as datetype

                                          RESIDENT lead;

                                         

                                        calendardeal:

                                        CONCATENATE

                                        LOAD

                                        deal_id as lead_id,

                                            ApplyMap('mapdealdatum',lead_id,Null()) as canonicaldate,

                                            'deal' as datetype

                                          RESIDENT lead;

                                        exit script;

                                          • Re: Canonical calendar issue
                                            A.M. van Keep

                                            Can you show me the new data-model with the following script:

                                             

                                            calendarlead:

                                            LOAD

                                            lead_id,

                                                ApplyMap('mapleaddatum',lead_id,Null()) as canonicaldate,

                                            'lead' as datetype

                                              RESIDENT lead;

                                             

                                            calendardeal:

                                            CONCATENATE (calendarlead)

                                            LOAD

                                            deal_id as lead_id,

                                                ApplyMap('mapdealdatum',lead_id,Null()) as canonicaldate,

                                                'deal' as datetype

                                              RESIDENT lead;

                                              • Re: Canonical calendar issue
                                                Jonnie Yacoub

                                                Here it is. i need something like this. Maybe it is not possible because of the left join between lead and deal table??

                                                [lead_id]   [canonicaldate] [datatype]

                                                5600          20/11/2017          lead

                                                5600          30/11/2017          deal

                                                Schermafbeelding 2017-11-30 om 15.51.07.png

                                                  • Re: Canonical calendar issue
                                                    A.M. van Keep

                                                    Hi Yonnie,

                                                     

                                                    I think you're right about questioning the join between lead and deal. What if you seperate them and you create the following bridge table:

                                                     

                                                    And you connect the Lead fields to "lead_id" and the Deal fields to "deal_id".

                                                     

                                                    Then you can use expression like:

                                                    COUNT({$<datatype={"deal"}>}  deal_id)

                                                    COUNT({$<datatype={"lead"}>}  lead_id)

                                                     

                                                    If you select dates in the cannonicaldate field you should be able to see changes in your dataset.

                                                     

                                                    datebridge:

                                                    LOAD

                                                    lead_id,

                                                    ApplyMap('mapleaddatum',lead_id,Null()) as canonicaldate,

                                                    'lead' as datetype

                                                      RESIDENT lead;


                                                     

                                                    CONCATENATE (datebridge) LOAD

                                                            deal_id,

                                                            ApplyMap('mapdealdatum',lead_id,Null()) as canonicaldate,

                                                            'deal' as datetype

                                                      RESIDENT lead;