Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have a table that contains two dates (Fig1) and i would like to calculate the days inbetween and display them like Fig2.
What is the best way to create this table? I have created a 'For Next' to loop through the records in the script, but for thousands of orders this is very slow. Can i just generate the table without creating it in load script?
Any help would be greatly received.
Thankyou
Fig1
OrderNo. | OrderDate | DeliveryDate |
---|---|---|
1111 | 01/04/2013 | 04/04/2013 |
2222 | 03/05/2013 | 05/05/2013 |
3333 | 04/05/2013 | 28/02/2014 |
4444 | 12/12/2013 | 04/03/2014 |
Fig2
OderNo | Date | Outstanding | Delivered |
---|---|---|---|
1111 | 01/04/2013 | 1 | |
1111 | 02/04/2013 | 1 | |
1111 | 03/04/2013 | 1 | |
1111 | 04/04/2013 | 1 | |
2222 | 03/05/2013 | 1 | |
2222 | 04/05/2013 | 1 | |
2222 | 05/05/2013 | 1 |
Load this into your script
Data:
Load * inline [
OrderNo, OrderDate, DeliveryDate
1111, 01/04/2013, 04/04/2013
2222, 03/05/2013, 05/05/2013
3333, 04/05/2013, 28/02/2014
4444, 12/12/2013, 04/03/2014];
DataFinal:
Load
OrderNo,
Date,
if(Date<DeliveryDate,1,null()) as Outstanding,
if(Date=DeliveryDate,1,null()) as Delivered;
Load
OrderNo,
OrderDate,
DeliveryDate,
Date(OrderDate+IterNo()-1) as Date
Resident Data
While OrderDate+IterNo()-1 <= DeliveryDate;
Drop Table Data;
why do you want to change? what is your objective?
Do you want to count how many days between two dates?
Vinay
Load this into your script
Data:
Load * inline [
OrderNo, OrderDate, DeliveryDate
1111, 01/04/2013, 04/04/2013
2222, 03/05/2013, 05/05/2013
3333, 04/05/2013, 28/02/2014
4444, 12/12/2013, 04/03/2014];
DataFinal:
Load
OrderNo,
Date,
if(Date<DeliveryDate,1,null()) as Outstanding,
if(Date=DeliveryDate,1,null()) as Delivered;
Load
OrderNo,
OrderDate,
DeliveryDate,
Date(OrderDate+IterNo()-1) as Date
Resident Data
While OrderDate+IterNo()-1 <= DeliveryDate;
Drop Table Data;
I want to be able to sum my outstanding orders and my delievered orders by day, week, month, year etc and be able to drill into those orders.
Hi Byron,
Wow that seems to work great, but i am struggling to understand how it works...where did the 'Date' field come from??
Many thanks
R
Load
OrderNo,
OrderDate,
DeliveryDate,
Date(OrderDate+IterNo()-1) as Date
Resident Data
While OrderDate+IterNo()-1 <= DeliveryDate;
Here the table is giving you dates for OrderDate upto DeliveryDate by iteration.
Now this Date is being used in Preceding Load to find out Outstanding and Delivered.
In other ways the above script can be used as below, but Preceding Load is much faster ...
Data:
Load * inline [
OrderNo, OrderDate, DeliveryDate
1111, 01/04/2013, 04/04/2013
2222, 03/05/2013, 05/05/2013
3333, 04/05/2013, 28/02/2014
4444, 12/12/2013, 04/03/2014];
DataFinal:
Load
OrderNo,
OrderDate,
DeliveryDate,
Date(OrderDate+IterNo()-1) as Date
Resident Data
While OrderDate+IterNo()-1 <= DeliveryDate;
FINAL:
Load
OrderNo,
Date,
if(Date<DeliveryDate,1,null()) as Outstanding,
if(Date=DeliveryDate,1,null()) as Delivered
Resident DataFinal;
Drop Table Data, DataFinal;
Glad it did mate!
The date field i created in the first load
Load
OrderNo,
OrderDate,
DeliveryDate,
Date(OrderDate+IterNo()-1) as Date
Resident Data
While OrderDate+IterNo()-1 <= DeliveryDate;
it creates a date for each date that appears between the conditional where clause. The -1 after iterno() is because iterno() starts with the value 1, and we want the first date not the next
While OrderDate+IterNo()-1 <= DeliveryDate;
This is where it does a loop like check and creates our new table
DataFinal:
Load
OrderNo,
Date,
if(Date<DeliveryDate,1,null()) as Outstanding,
if(Date=DeliveryDate,1,null()) as Delivered;
Load
OrderNo,
OrderDate,
DeliveryDate,
Date(OrderDate+IterNo()-1) as Date
Resident Data
While OrderDate+IterNo()-1 <= DeliveryDate;
The preceding load is where we get the final result you want. Hope that clears it up for you. Let me know
Cheers,
Byron
Byron
Thanks so much for your explanation and the time you have taken to reply.
Greatly appreciated!!
R