topic Re: Adding Rows for Each Day in QlikView

Adding Rows for Each Day

Fri, 06 Dec 2013 22:05:37 GMT

Hi,

I have a table which has two columns.

1) Date

2) ID

For some days the ID's are missing. I want to add rows to the missing Days. I am using this formula, but it is not working. Not sure what the issue.

MinMaxDate:

Load Min(Date__Id) as MinDate, Max(Date__Id) as MaxDate resident Fact;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Drop Table MinMaxDate;

Join (Fact)

Load Date(recno()+$(vMinDate)) as Date__Id Autogenerate vMaxDate - vMinDate;

Class:

NoConcatenate Load Date__Id,

If( IsNull([Class__Id] ),peek([Class__Id] ), [Class__Id] ) as [Class__Id]

Resident AccountFact

group by [Class__Id] , Date__Id

Order By [Class__Id] ,Date__Id ;

Any help?

Thanks.

Re: Adding Rows for Each Day

afurtado — Fri, 06 Dec 2013 22:46:06 GMT

Hi.

Could you put a sample (.qvw)?

Alessandro Furtado

Re: Adding Rows for Each Day

clisboa_noesis — Fri, 06 Dec 2013 23:06:36 GMT

Hi,

All you need to do is to generate a calendar, or simply a list of dates and then left join your table with that calendar.

Regards,

Carlos

Re: Re: Adding Rows for Each Day

Fri, 06 Dec 2013 23:18:45 GMT

Hi,

Here is the sample file.

As you see..for ID 18 I don't have a record for September 2nd. I want one record for each day.

Thanks for your help in advance.

Re: Adding Rows for Each Day

Sat, 07 Dec 2013 19:50:06 GMT

Please find the below sample script:

TEMP:

LOAD * INLINE [

ID, DATE

1, 10/1/2013

1, 10/2/2013

1, 10/4/2013

1, 10/6/2013

2, 10/1/2013

2, 10/2/2013

2, 10/5/2013

];

TEMP1:

LOAD ID,

Max(DATE) AS MAXDATE ,

Min(DATE) AS MINDATE

Resident TEMP

group by ID;

DROP Table TEMP;

FINAL:

LOAD ID,

Date(MINDATE + IterNo() - 1) AS DATE

Resident TEMP1 While IterNo() <= (MAXDATE - MINDATE) + 1 ;

DROP Table TEMP1;

Re: Adding Rows for Each Day

Sun, 08 Dec 2013 04:54:20 GMT

Thanks. It worked.