Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Giood
I know this question might've been asked a million times before but nothing seems to help I want to create a rolling 12 month chart but what I want to do first is to autonumber my date entries sequentially i.e 2134 is assigned to 12 Dec 2012 and 2135 is assigned to 13 Dec 2013, but it seems like its a mission at the moment cause this preceding load currently doesn't assign the ID in a sequential mode.
Load
*,
AutoNumber([Day Of Order]) as [MonthID];
here is my complete code below:
let vBudget2008=300000;
let vBudget2009=500000;
let vBudget2010=600000;
let vBudget2011=800000;
let vBudget2012=400000;
let vBudget2013=600000;
let vBudget2014=700000;
let vBudget2015=800000;
let vBudget2016=800000;
//Preceding load in order to use derived fields [Actaual Delivery Date] and [Ant. Day Of Delivery]
Pre_load:
Load*,
if (([Actaual Delivery Date]- [Ant. Day Of Delivery])=0,'Delivered on Time','Late Delivery') as [Delivery Period];
Load
*,
AutoNumber([Day Of Order]) as [MonthID];
Main:
LOAD
[Customer ID],
Customer,
[Product Line],
[Product Group],
Product,
Date(date#([Day Ordered],'DD/MM/YYYY')) as [Day Of Order],
[No. Of Sales],
[Sales Rep.],
Date(Date#([Promised Day of Delivery],'DD/MM/YYYY')) as [Ant. Day Of Delivery],
Date(Date#([Actual Day Delivered],'DD/MM/YYYY')) as [Actaual Delivery Date],
[Division In Country],
Country,
Region,
Province,
Price,
AutoNumberHash256([Customer ID], Customer) as [Repeat Customers]
FROM
[ACME Inc. 26 Jul 2016.xlsx]
(ooxml, embedded labels, table is [BMW (65)]);
//Create Master Calendar to get Month, Year, Week, Day and Quarter
Mas_Cal:
LOAD
Date([Day Of Order]) as [Day Of Order],
Year([Day Of Order]) as Year,
'Q' & Ceil(Month([Day Of Order])/3) as Quarter,
Month([Day Of Order]) as Month,
Day([Day Of Order]) as Day,
Week([Day Of Order]) as Week;
LOAD
Date(MinDate + Iterno() -1) as [Day Of Order]
While(MinDate + Iterno() -1)<=Num(MaxDate);
LOAD
Min([Day Of Order]) as MinDate,
Max([Day Of Order]) as MaxDate
Resident Main;
Thanks in advance!
Well AutoNumber will assign sequential number in the order of the load statement. For example if you have dates like this
Date, AutoNumber
13 Dec 2015, 1
12 Dec 2015, 2
14 Dec 2015, 3
21 Dec 2015, 4
AutoNumber doesn't know what sort order you want. In order to get a desired AutoNumber, you will need to sort the load statement in a resident load using Order By Statement.
Try something along these lines:
Main:
LOAD [Customer ID],
Customer,
[Product Line],
[Product Group],
Product,
Date(Date#([Day Ordered],'DD/MM/YYYY')) as [Day Of Order],
[No. Of Sales],
[Sales Rep.],
Date(Date#([Promised Day of Delivery],'DD/MM/YYYY')) as [Ant. Day Of Delivery],
Date(Date#([Actual Day Delivered],'DD/MM/YYYY')) as [Actaual Delivery Date],
[Division In Country],
Country,
Region,
Province,
Price,
AutoNumberHash256([Customer ID], Customer) as [Repeat Customers]
FROM [ACME Inc. 26 Jul 2016.xlsx]
(ooxml, embedded labels, table is [BMW (65)]);
Pre_load:
LOAD *,
If(([Actaual Delivery Date] - [Ant. Day Of Delivery]) = 0, 'Delivered on Time', 'Late Delivery') as [Delivery Period];
LOAD *,
AutoNumber([Day Of Order]) as [MonthID]
Resident Main
Order By [Day Of Order];
DROP Table Main;
And then you can continue onto creating a calendar for yourself the same way you have done this before.
HTH
Best,
Sunny
Well AutoNumber will assign sequential number in the order of the load statement. For example if you have dates like this
Date, AutoNumber
13 Dec 2015, 1
12 Dec 2015, 2
14 Dec 2015, 3
21 Dec 2015, 4
AutoNumber doesn't know what sort order you want. In order to get a desired AutoNumber, you will need to sort the load statement in a resident load using Order By Statement.
Try something along these lines:
Main:
LOAD [Customer ID],
Customer,
[Product Line],
[Product Group],
Product,
Date(Date#([Day Ordered],'DD/MM/YYYY')) as [Day Of Order],
[No. Of Sales],
[Sales Rep.],
Date(Date#([Promised Day of Delivery],'DD/MM/YYYY')) as [Ant. Day Of Delivery],
Date(Date#([Actual Day Delivered],'DD/MM/YYYY')) as [Actaual Delivery Date],
[Division In Country],
Country,
Region,
Province,
Price,
AutoNumberHash256([Customer ID], Customer) as [Repeat Customers]
FROM [ACME Inc. 26 Jul 2016.xlsx]
(ooxml, embedded labels, table is [BMW (65)]);
Pre_load:
LOAD *,
If(([Actaual Delivery Date] - [Ant. Day Of Delivery]) = 0, 'Delivered on Time', 'Late Delivery') as [Delivery Period];
LOAD *,
AutoNumber([Day Of Order]) as [MonthID]
Resident Main
Order By [Day Of Order];
DROP Table Main;
And then you can continue onto creating a calendar for yourself the same way you have done this before.
HTH
Best,
Sunny
Oh i see Sunny you're a genious it actually worked!
I guess I need to start broadening my thinking a bit.