Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Adding Rows for Each Day

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.

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

5 Replies
afurtado
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi.

Could you put a sample (.qvw)?

Alessandro Furtado

furtado@farolbi.com.br
clisboa_noesis
Partner - Creator
Partner - Creator

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

Not applicable
Author

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.

Not applicable
Author

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;

Not applicable
Author

Thanks. It worked.