Qlik Community

Qlik Sense Cloud Discussions

Highlighted
jonnieyacoub
Contributor

Canonical calendar issue

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;

1 Solution

Accepted Solutions
avkeep01
Valued Contributor

Re: Canonical calendar issue

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;

18 Replies
robert99
Valued Contributor II

Re: Canonical calendar issue

Hi

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

Canonical Date

shraddha_g
Honored Contributor III

Re: Canonical calendar issue

What is issue you are facing?

jonnieyacoub
Contributor

Re: Canonical calendar issue

Hi Robert,

Yeah, i have read the post like ten times. And still it wont work in my situation

jonnieyacoub
Contributor

Re: Canonical calendar issue

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

avkeep01
Valued Contributor

Re: Canonical calendar issue

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;

jonnieyacoub
Contributor

Re: Canonical calendar issue

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;

avkeep01
Valued Contributor

Re: Canonical calendar issue

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;

jonnieyacoub
Contributor

Re: Canonical calendar issue

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

avkeep01
Valued Contributor

Re: Canonical calendar issue

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;