Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Adding Rows for Each Day

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;

5 Replies
MVP
MVP

Re: Adding Rows for Each Day

Hi.

Could you put a sample (.qvw)?

Alessandro Furtado

clisboa_noesis
Contributor

Re: Adding Rows for Each Day

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

Re: Re: Adding Rows for Each Day

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

Re: Adding Rows for Each Day

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

Re: Adding Rows for Each Day

Thanks. It worked.

Community Browser