Urgent....!

Mahamed_Qlik · ‎2015-08-07

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.

MK_QSL · ‎2015-08-07

How you want the final output?

If you select Chassis Number, you want only Month End or other data also.

Gysbert_Wassenaar · ‎2015-08-07

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]

;

talk is cheap, supply exceeds demand

lironbaram · ‎2015-08-07

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;

Mahamed_Qlik · ‎2015-08-07

thank you buddy.perfect answer

Mahamed_Qlik · ‎2015-08-07

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

lironbaram · ‎2015-08-07

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