topic Yearname Issue in QlikView

Yearname Issue

Karl_Hart — Thu, 26 Feb 2015 11:23:11 GMT

Hi all,

I have a database with a load of rent charges in. I have

start date

end date

amount

I have applied Yearname([str-dte],0, 4) as RentYear to get the financial year to which the rent relates.

I am having an issue with the following scenario

Start Date 10/07/2012

End Date 09/07/2014

Amount £100

Start Date 10/07/2014

End Date 09/07/2015

Amount £110

This gives me

Rent Year 2012-2013 Amount £100

Rent Year 2014-2015 Amount £110

Because the start date doesn't fall into 2013-2014 it isn't generating an amount for that year.

How Do i get the following (correct) result?

Rent Year 2012-2013 Amount £100

Rent Year 2013-2014 Amount £100

Rent Year 2014-2015 Amount £110

Many thanks.

Re: Yearname Issue

anbu1984 — Thu, 26 Feb 2015 11:40:07 GMT

Table:

Load YearName(StartDate,0,4) As RentYear,*;

Load Date#(StartDate,'DD/MM/YYYY') As StartDate,Date#(EndDate,'DD/MM/YYYY') As EndDate,Amount Inline [

StartDate,EndDate,Amount

10/07/2012,09/07/2014,£100

10/07/2014,09/07/2015,£110 ];

Concatenate

Load StartDate,EndDate,Amount,YearName(AddYears(StartDate,IterNo()),0,4) As RentYear Resident Table While Year(EndDate) - Year(StartDate) -IterNo() >= 1;

Re: Yearname Issue

Karl_Hart — Thu, 26 Feb 2015 11:56:31 GMT

Thanks for that anbu,

That works great for the example given, but how do i replace the inline bit in your script when I have a full database of data to go in there?

Re: Yearname Issue

anbu1984 — Thu, 26 Feb 2015 12:13:48 GMT

Table:

Sql Select * From Table;

Concatenate

Load StartDate,EndDate,Amount,YearName(AddYears(StartDate,IterNo()),0,4) As RentYear Resident Table While Year(EndDate) - Year(StartDate) -IterNo() >= 1;