topic Need Help ....... in QlikView

Need Help .......

alvinford — Tue, 11 Dec 2012 15:19:23 GMT

Hi All,

I am facing the following issue, request your help. I have the data which is shown as below.

OfficeStartDate	OfficeEndDate	ContractExtensionDate
16/01/2010	15/04/2013	15/12/2012

Based on the above data my desired output will be as below

OfficeStartDate	OfficeEndDate
16/1/2010	15/12/2012
16/12/2012	15/4/2013

If ContractExtensionDate is less than my OfficeEndDate then I have to pick up the ContractExtensionDate as my OfficeEndDate. And ContractExtensionDate +1 will be my OfficeStartDate (In New Row) . And my office end date will be my end Date as shown above.

Thanks in Advance...

Regards,

Alvin.

Re: Need Help .......

CELAMBARASAN — Tue, 11 Dec 2012 15:42:22 GMT

Try with this

Load

Pick(iterno(), OfficeStartDate, Date(Num(ContractExtensionDate) + 1)) AS OfficeStartDate,

Pick(iterno(), if(ContractExtensionDate < OfficeEndDate, ContractExtensionDate, OfficeEndDate), OfficeEndDate)

AS OfficeEndDate

Resident TempTable //Assuming your original data is temp table

While

(ContractExtensionDate < OfficeEndDate or iterno() = 1) and iterno() <= 2;

Hope it helps

Re: Need Help .......

alvinford — Tue, 11 Dec 2012 16:24:46 GMT

Hi Adhimulam,

Very thanks for the reply its helps me . But I have a small concern I have applied the same logic for other rows also but its giving a slight different answer .Below is the example.

Desired Output

OfficeStartDate	OfficeEndDate
01/01/2010	31/12/2012
01/05/2012	31/12/2012
01/05/2012	31/12/2012
01/01/2013	28/02/2013

Current Output According to your logic . I have underlined the row which there is a difference.

OfficeStartDate	OfficeEndDate
01/01/2010	31/12/2012
01/05/2012	31/12/2012
01/01/2013	31/12/2012
01/01/2013	28/02/2013

Can you please help me to solve this .

Thanks very much for your help.

Regards,

Alvin.

Re: Need Help .......

CELAMBARASAN — Tue, 11 Dec 2012 17:33:32 GMT

Try with this

Load

Pick(iterno(), OfficeStartDate, Date(Num(ContractExtensionDate) + 1)) AS OfficeStartDate,

Pick(iterno(), if(ContractExtensionDate < OfficeEndDate

and ContractExtensionDate > OfficeStartDate

, ContractExtensionDate,OfficeEndDate), OfficeEndDate)

AS OfficeEndDate

Resident TempTable //Assuming your original data is temp table

While

((ContractExtensionDate < OfficeEndDate and ContractExtensionDate > OfficeStartDate) or iterno() = 1) and iterno() <= 2;