Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have some sales data stored in a table in date order
Date Sales
01-01-2017 5000
02-01-2017 10000
......
31-08-2017 12000
I want to extract data for the following Periods
1. 1st to 14th of the month Eg 01-01-17 to 14-01-17
2. 15th to 31st of the month Eg: 15-01-17 to 31-01-17
3. 15th to 14th (two months) Eg : 15-01-17 to 14-02-17
How can I do this in script or master calendar pls help
Hi Upali,
See Attachment.
I changed Section 'Calendar' of Script.
Remove Replace from RISKCALENDAR Table in Script.
Regards,
Antonio
Try some thing like below?
Fact:
Load Date, Sales from Fact;
Concatenate (Fact)
Load If(Date >= '01-01-17' and <='14-01-17', Date) as Date,
Sales,
'Period 1' as Flag
Resident Fact;
Concatenate (Fact)
Load If(Date >= '15-01-17' and <='31-01-17', Date) as Date,
Sales,
'Period 2' as Flag
Resident Fact;
Concatenate (Fact)
Load If(Date >= '15-01-17' and <='14-02-17', Date) as Date,
Sales,
'Period 3' as Flag
Resident Fact;
Hi Anil,
I can do what you suggest for small date range .But In my data table I have millions of records ranging 1 -15 years. So it is practically difficult to do it.Is there a way to do it through master calendar ?
Even, If you need master calendar off course, Bucket needed separately. And i believe you don't require master calendar over here. Can you provide more information related the same..
Anil
I have attached my sample data .I want Period Brackets as shown above to be against RISK_DATE. Kindly help me with best possible solution
Your buckets are fixed? I mean, 1 to 15, 15 to 31, 15 to 14 for two months?
Yes
Is it ok if the same can be performed on front end, instead creating Flag in script?
Better If could be done as script level, Anyway pls make available your suggested method
hi I have Used this Logic with Demo of Master calender and its working fine Load If(Date >= '01/01/2017' and Date <='14/01/2017', Date) as Date, | |
'Period 1' as Flag |
Resident MasterCalendar;
Load If(Date >= '15/01/2017' and Date<='31/01/2017', Date) as Date, | |
'Period 2' as Flag |
Resident MasterCalendar;
Load If(Date >= '15/01/2017' and Date <='14/02/2017', Date) as Date, | |
'Period 3' as Flag |
Resident MasterCalendar;