Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello! All details in video
Using this method:
https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date
What is my problem now:
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;
Finaly, win .
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;
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.
Stil don't understand. In example loading to DataBridge going from single table: OrderLines
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;
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.
So, what to do in my case?
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;
I want to see Price and AccuralSum in one timeline at same time. Like this:
Finaly, win .
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;