Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Karl_Hart
Creator
Creator

Yearname Issue

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.

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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;

View solution in original post

3 Replies
anbu1984
Master III
Master III

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;

Karl_Hart
Creator
Creator
Author

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?

anbu1984
Master III
Master III

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;