Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have scenario in which there is distinct chassis no. which has creation date and dispatch date.
Creation Date: Date on which the vehicle is created and assigned with unique chassis no.
Dispatch Date: Date on which the vehicle is dispatched for selling.
Example: In below table the vehicle with the chassis number 1235 is created on 02-04-2014 and dispatched on 02-07-2014
I have following data:
Creation Date | Dispatch Date | Chassis no. |
03-07-2013 | 03-06-2014 | 1234 |
02-04-2014 | 02-07-2014 | 1235 |
15-09-2015 | 15-09-2015 | 1236 |
03-04-2015 | 16-05-2015 | 1237 |
Now, I want to create one field in script named as "Month_End", in which it should include all the month end date between creation date and a month before month of dispatched date.
For Example:
In above table, row no 1 actual month difference between Creation Date and Dispatch Date is 11 months so i need field "Month_End" in as follows :
Month End |
---|
31-07-2013 |
31-08-2013 |
30-09-2013 |
31-10-2013 |
30-11-2013 |
31-12-2013 |
31-01-2014 |
28-02-2014 |
31-03-2014 |
30-04-2014 |
31-05-2014 |
Kindly Help.
How you want the final output?
If you select Chassis Number, you want only Month End or other data also.
Something like this should do the trick:
LOAD
[Creation Date],
[Dispatch Date],
[Chassis no.],
MonthEnd([Creation Date], Iterno() -1) As Month_End
FROM ...
While MonthEnd([Creation Date], Iterno() -1) <= [Dispatch Date]
;
hi
this is the script and a demo model
Data:
LOAD * INLINE [
Creation_Date, Dispatch_Date, Chassis_no
3/7/2013, 3/6/2014, 1234
2/4/2014, 2/7/2014, 1235
15/9/2015, 15/9/2015, 1236
3/4/2015, 16/5/2015, 1237
];
left join
load Chassis_no,
MonthEnd(AddMonths(Creation_Date,IterNo()-1)) AS MonthEnd
Resident Data
While MonthEnd(AddMonths(Creation_Date,IterNo()-1))<=Dispatch_Date;
thank you buddy.perfect answer
Hi Guysbert,
I have one more scenario where we some chassis no only having Creation Date and Dispatch Date is missing and that Chassis no should also consider.
The data should be like :
Creation Date | Dispatch Date | Chassis no. |
03-07-2013 | 03-06-2014 | 1234 |
02-04-2014 | 1235 | |
15-09-2015 | 15-09-2015 | 1236 |
03-04-2015 | 16-05-2015 | 1237 |
If I try to using above provided your logic then it works fine for all the chassis no which are having Dispatch Date, now suppose in above table chassis no 1235 doesn't having Dispatch date and should be consider then ?
Kindly Help
hi
this script will solve your issue because it's calculate the month in two steps
first load all data , and then add months to each row
Data:
LOAD * INLINE [
Creation_Date, Dispatch_Date, Chassis_no
3/7/2013, 3/6/2014, 1234
2/4/2014, 2/7/2014, 1235
15/9/2015, 15/9/2015, 1236
3/4/2015, 16/5/2015, 1237
];
left join
load Chassis_no,
MonthEnd(AddMonths(Creation_Date,IterNo()-1)) AS MonthEnd
Resident Data
While MonthEnd(AddMonths(Creation_Date,IterNo()-1))<=Dispatch_Date;