topic Re: How to generate rows in this case? in QlikView

How to generate rows in this case?

Wed, 17 Dec 2014 20:57:32 GMT

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.

Re: How to generate rows in this case?

maxgro — Wed, 17 Dec 2014 21:01:43 GMT

look at this doc, page 10

Generating Missing Data In QlikView

Re: How to generate rows in this case?

jpapador — Wed, 17 Dec 2014 21:06:55 GMT

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.

Re: How to generate rows in this case?

MarcoWedel — Thu, 18 Dec 2014 00:36:37 GMT

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

Re: How to generate rows in this case?

Thu, 18 Dec 2014 13:08:43 GMT

Thank you very much Marco, that worked perfect!

Re: How to generate rows in this case?

MarcoWedel — Thu, 18 Dec 2014 13:11:34 GMT

You're welcome

regards

Marco