Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have 2 fact tables, PricingReport and TradeReport - not all date values in PricingReport exist in TradeReport. Therefore, I would like to create a master calendar which covers all dates. I can create the calendar for 1 fact table (e.g. PricingReport) like below, but how can I add it also for TradeReport?
tmpMinMaxDates:
LOAD
MIN(Num(Date)) AS MinDate,
Max(Num(Date)) AS MaxDate
RESIDENT
PricingReport;
LET varMinDate = PEEK('MinDate');
LET varMaxDate = PEEK('MaxDate');
DROP TABLE [tmpMinMaxDates];
tmpCalendar:
LOAD
DATE($(varMinDate) + RowNo() - 1) AS Date
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;
Left Join
Calendar:
LOAD
day(Date) as DayNumber,
WeekDay(Date) as Day,
month(Date) as Month,
Year(Date) as Year,
date(WeekEnd(Date,0,-2),'D/M') as Week
Resident tmpCalendar;
Maybe you should consider to concatenate or join Pricing and Trade reports into one table?
Maybe you should consider to concatenate or join Pricing and Trade reports into one table?
Concatanate these two fact tables and then link it with master calender with the date field.
Hi
Why you can try like this
Take min and max value both the table and from that, you can able to find the minimum and maximum values among those values.
Then you can generate the calendar based on that.
i.e. Common calendar for both the table by using minimum and maximum date range.
Hope it helps
Try using the FieldValue and FieldValueCount functions:
Temp:
LOAD
min(FieldValue('Date', recno())) as MinDate,
max(FieldValue('Date', recno())) as MaxDate
AUTOGENERATE FieldValueCount('Date');
LET varMinDate = PEEK('MinDate');
LET varMaxDate = PEEK('MaxDate');
As long as your fact tables use the same name for the Date field you will get the min and max date over both fact tables.
Hi Sifat,
Call these fields by a single name for example here in this case i m considering name as Datefield.
You this scrip for master calender:
LET vMinDate = num(makedate(2005));
LET vMaxDate = num(today());
Datefield:
LOAD
$(vMinDate) + IterNo() -1 as Datefield
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);
Calendar:
LOAD
Datefield as %Date,
date(Datefield) as Date,
year(Datefield) as Year,
month(Datefield) as Month,
day(Datefield) as Day,
week(Datefield) as Week,
weekday(Datefield) as Weekday
RESIDENT Datefield;
drop table Datefield;
Thanks,
AS
how to create peek varible