Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to generate rows in this case?

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.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

QlikCommunity_Thread_146193_Pic1.JPG

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

View solution in original post

5 Replies
maxgro
MVP
MVP

look at this doc, page 10

Generating Missing Data In QlikView

jpapador
Partner
Partner

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

Hi,

QlikCommunity_Thread_146193_Pic1.JPG

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

Not applicable
Author

Thank you very much Marco, that worked perfect!

MarcoWedel

You're welcome

regards

Marco