Adding Rows for Each Day

[#1]

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.

[afurtado]

Hi.

Could you put a sample (.qvw)?

Alessandro Furtado

[clisboa_noesis]

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

[#4]

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.

[#5 · Accepted solution]

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;

[#6]

Thanks. It worked.