topic Re: Urgent....! in QlikView

Urgent....!

Mahamed_Qlik — Fri, 07 Aug 2015 10:25:53 GMT

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.

Re: Urgent....!

MK_QSL — Fri, 07 Aug 2015 10:46:55 GMT

How you want the final output?

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

Re: Urgent....!

Gysbert_Wassenaar — Fri, 07 Aug 2015 10:49:26 GMT

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]

;

Re: Urgent....!

lironbaram — Fri, 07 Aug 2015 10:54:32 GMT

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;

Re: Urgent....!

Mahamed_Qlik — Fri, 07 Aug 2015 11:30:21 GMT

thank you buddy.perfect answer

Re: Urgent....!

Mahamed_Qlik — Fri, 07 Aug 2015 13:50:05 GMT

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

Re: Urgent....!

lironbaram — Sat, 08 Aug 2015 03:09:20 GMT

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;