Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

evgeniystuchalk
Contributor II

Need help with Canonical date. What i'm doing wrong?

Hello! All details in video

Using this method:

https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

What is my problem now:

e8ffa465b8.jpg

920c28b959.jpg

897981d85a.jpg

My code, if you want to run it on your machine (not required any SETtings)

SET DateFormat='DD.MM.YYYY';

Deals:

LOAD * INLINE [Price , DealDay , MonthInPack, OrderLineID, DealID

   

6000, 18.02.2016,6,1,1

5000, 18.02.2016,4,2,2

];

Accural:

LOAD

  DealID,

  Price / MonthInPack AS AccuralSum,

    Date( AddMonths(DealDay, iterno()-1) ) AS AccuralDay

RESIDENT Deals

WHILE iterno() <= MonthInPack;

DealID2DealDay:

MAPPING LOAD

DealID,

DealDay

RESIDENT Deals;

DealID2AccuralDay:

MAPPING LOAD

DealID,

AccuralDay

RESIDENT Accural;

DateBridge:

Load OrderLineID, Applymap('DealID2DealDay',DealID,Null()) as CanonicalDate, 'Order' as DateType

Resident Deals;

Load OrderLineID, Applymap('DealID2AccuralDay',DealID,Null()) as CanonicalDate, 'Accural' as DateType

Resident Deals;

1 Solution

Accepted Solutions
evgeniystuchalk
Contributor II

Re: Need help with Canonical date. What i'm doing wrong?

Finaly, win .

1ef3910f94.jpg

Resume:

1) Data Model. I moved field 'AccuralSum' to Deals Table. Instead of placing this value in Accural table.

It gives to Qlik opportunity to count one Accural value per Deal in AccuralDay via cannoincal date. Instead of summing all 'AccuralSum's at each canonical date (like in screen above this post).

2) DataBridge. I've changed mapping rules. First mapping rules was like this (taked it blindly from post with example):

DealID2DealDay:

MAPPING LOAD

DealID,

DealDay

RESIDENT Deals;

DealID2AccuralDay:

MAPPING LOAD

DealID,

AccuralDay

RESIDENT Accural;

It dont works, because one deal have multiple 'AccuralDay' values, and applying this map just return to me first accural date for each deals (thanks to gwassenaar form helping me to realise it. ). It works properly only if you have only one date of each type for one deal.

So, instead loading date via Applymap, i'm loading OrderLineID, to make connections between OrderLineID and accural date, because one OrderLineID always have one DealID:

OrderLineID2DealID:

MAPPING LOAD

DealID,

OrderLineID

RESIDENT Deals;

DateBridge:

Load OrderLineID, DealDay as CanonicalDate, 'Order' as DateType

Resident Deals;

Load Applymap('OrderLineID2DealID',DealID,Null()) as OrderLineID, AccuralDay as CanonicalDate, 'Accural' as DateType

Resident Accural;


Final Code:


SET DateFormat='DD.MM.YYYY';

Deals:

LOAD * INLINE [Price , DealDay , MonthInPack, OrderLineID, DealID, AccuralSum

  

6000, 18.02.2016,6,1,1234,1000

5000, 18.02.2016,4,2,2456,1250

];

Accural:

LOAD

  DealID,

  

    Date( AddMonths(DealDay, iterno()) ) AS AccuralDay

RESIDENT Deals

WHILE iterno() <= MonthInPack;

OrderLineID2DealID:

MAPPING LOAD

DealID,

OrderLineID

RESIDENT Deals;

DateBridge:

Load OrderLineID, DealDay as CanonicalDate, 'Order' as DateType

Resident Deals;

Load Applymap('OrderLineID2DealID',DealID,Null()) as OrderLineID, AccuralDay as CanonicalDate, 'Accural' as DateType

Resident Accural;

Calendar code:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

   

Temp: 

Load 

min(CanonicalDate) as minDate, 

max(CanonicalDate) as maxDate 

Resident [DateBridge]; 

     

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

     

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

     

[CanonicalCalendar]: 

Load 

  date(TempDate, 'D MMM YY') AS CanonicalDate, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [CanonicalWeek], 

  Year(TempDate) As [CanonicalYear], 

  date(MonthStart(TempDate),'MMM-YY') As [CanonicalMonth], 

  ApplyMap('QuartersMap', month(TempDate), Null()) as [CanonicalQuarter]

 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;


7 Replies

Re: Need help with Canonical date. What i'm doing wrong?

You only load records from the Deals table into the DateBridge table. Since there are only two dates in the Deals table you get only two dates in the DateBridge table.


talk is cheap, supply exceeds demand
evgeniystuchalk
Contributor II

Re: Need help with Canonical date. What i'm doing wrong?

Stil don't understand. In example loading to DataBridge going from single table: OrderLines

Full+model.png

Code from example:

     DateBridge:

     Load OrderLineID, Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate, 'Order' as DateType

          Resident OrderLines;

     Load OrderLineID, Applymap('OrderID2RequiredDate',OrderID,Null()) as CanonicalDate, 'Required' as DateType

          Resident OrderLines;

     Load OrderLineID, ShippedDate as CanonicalDate, 'Shipped' as DateType

          Resident OrderLines;

I don't understand, what exactly wrong in my code;

Re: Need help with Canonical date. What i'm doing wrong?

In the example from the blog post there will be only ONE OrderDate for each OrderLineID. An order cannot have more than one orderdate. That's why the mapping table and applymap can be used. In your data you create a mapping table with multiple records for each OrderLineID. Applymap can return only one result. All it does is search-and-replace. It will not create new records. The ApplyMap function will get the first matching result from the mapping table and then it's done.


talk is cheap, supply exceeds demand
evgeniystuchalk
Contributor II

Re: Need help with Canonical date. What i'm doing wrong?

So, what to do in my case?

Re: Need help with Canonical date. What i'm doing wrong?

I don't understand why you need a date bridge table in the first place. You can for example do this:

Deals:

LOAD

  Price , DealDay as Day , MonthInPack, OrderLineID, DealID, 'Deal' as Type

INLINE [

Price , DealDay , MonthInPack, OrderLineID, DealID

6000, 18.02.2016,6,1,1

5000, 18.02.2016,4,2,2

];

CONCATENATE(Deals)

LOAD

  DealID,

  Price / MonthInPack AS AccuralSum,

    Date( AddMonths(DealDay, iterno()-1) ) AS Day,

    'Accural' as Type

RESIDENT Deals

WHILE iterno() <= MonthInPack;


talk is cheap, supply exceeds demand
evgeniystuchalk
Contributor II

Re: Need help with Canonical date. What i'm doing wrong?

I want to see Price and AccuralSum in one timeline at same time. Like this:

e6151ff584.jpg

evgeniystuchalk
Contributor II

Re: Need help with Canonical date. What i'm doing wrong?

Finaly, win .

1ef3910f94.jpg

Resume:

1) Data Model. I moved field 'AccuralSum' to Deals Table. Instead of placing this value in Accural table.

It gives to Qlik opportunity to count one Accural value per Deal in AccuralDay via cannoincal date. Instead of summing all 'AccuralSum's at each canonical date (like in screen above this post).

2) DataBridge. I've changed mapping rules. First mapping rules was like this (taked it blindly from post with example):

DealID2DealDay:

MAPPING LOAD

DealID,

DealDay

RESIDENT Deals;

DealID2AccuralDay:

MAPPING LOAD

DealID,

AccuralDay

RESIDENT Accural;

It dont works, because one deal have multiple 'AccuralDay' values, and applying this map just return to me first accural date for each deals (thanks to gwassenaar form helping me to realise it. ). It works properly only if you have only one date of each type for one deal.

So, instead loading date via Applymap, i'm loading OrderLineID, to make connections between OrderLineID and accural date, because one OrderLineID always have one DealID:

OrderLineID2DealID:

MAPPING LOAD

DealID,

OrderLineID

RESIDENT Deals;

DateBridge:

Load OrderLineID, DealDay as CanonicalDate, 'Order' as DateType

Resident Deals;

Load Applymap('OrderLineID2DealID',DealID,Null()) as OrderLineID, AccuralDay as CanonicalDate, 'Accural' as DateType

Resident Accural;


Final Code:


SET DateFormat='DD.MM.YYYY';

Deals:

LOAD * INLINE [Price , DealDay , MonthInPack, OrderLineID, DealID, AccuralSum

  

6000, 18.02.2016,6,1,1234,1000

5000, 18.02.2016,4,2,2456,1250

];

Accural:

LOAD

  DealID,

  

    Date( AddMonths(DealDay, iterno()) ) AS AccuralDay

RESIDENT Deals

WHILE iterno() <= MonthInPack;

OrderLineID2DealID:

MAPPING LOAD

DealID,

OrderLineID

RESIDENT Deals;

DateBridge:

Load OrderLineID, DealDay as CanonicalDate, 'Order' as DateType

Resident Deals;

Load Applymap('OrderLineID2DealID',DealID,Null()) as OrderLineID, AccuralDay as CanonicalDate, 'Accural' as DateType

Resident Accural;

Calendar code:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

   

Temp: 

Load 

min(CanonicalDate) as minDate, 

max(CanonicalDate) as maxDate 

Resident [DateBridge]; 

     

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

     

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

     

[CanonicalCalendar]: 

Load 

  date(TempDate, 'D MMM YY') AS CanonicalDate, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [CanonicalWeek], 

  Year(TempDate) As [CanonicalYear], 

  date(MonthStart(TempDate),'MMM-YY') As [CanonicalMonth], 

  ApplyMap('QuartersMap', month(TempDate), Null()) as [CanonicalQuarter]

 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;