How to generate rows in this case?

[#1]

Hello Community, i am having trouble with a document i am working on.

I have this table (i am just giving an example, the table is bigger) where i have an id_person, an initial date and a Final_Date. I have alredy made some scripting to get the initial period and the final period for each id and concatenated these periods in a field called Period. The thing that i need to solve now is to generate the periods missing between the initial date and the final date. I only need to generate the Period field, the date fields would remain the same.

This is the example of how my data is

And this is how i want it to be:

So, the id_person 1 would have as many registers as periods it has, and the id_person 2 would remain the same because is only one period.

Please, if someone can help me, I can´t work out the solution.

[maxgro]

look at this doc, page 10

Generating Missing Data In QlikView

[jpapador]

If you had a complete list of periods you could use interval match.  You would also have to assign a day to the period.

Example:

Table1:

LOAD

      id_person,

     Initial_Date,

     Final_Date

FROM Datasource

Table2:

LOAD

        MakeDate(Left(Period, 4), Right(Period, 2), 1) as PeriodDate

FROM Distinct list of periods

Left Join (Table1)

IntervalMatch(PeriodDate)

LOAD

     Initial_Date,

     Final_Date

Resident Table1;

This will return all of the periods between initial date and end date for each combination.

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

LOAD *,
    Date(AddMonths(MonthName(Initial_Date),IterNo()-1),'YYYYMM') as Period
Inline [
id_person, Initial_Date, Final_Date
1,02/07/2014, 23/11/2014
2,19/06/2014, 20/06/2014
]
While AddMonths(MonthName(Initial_Date),IterNo()-1)<=Final_Date;

hope this helps

regards

Marco

[#5]

Thank you very much Marco, that worked perfect!

[MarcoWedel]

You're welcome

regards

Marco