Discussion Board for collaboration on QlikView Scripting.
I have a table which has two columns.
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.
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;
Load Date(recno()+$(vMinDate)) as Date__Id Autogenerate vMaxDate - vMinDate;
NoConcatenate Load Date__Id,
If( IsNull([Class__Id] ),peek([Class__Id] ), [Class__Id] ) as [Class__Id]
group by [Class__Id] , Date__Id
Order By [Class__Id] ,Date__Id ;
Go to Solution.
Please find the below sample script:
LOAD * INLINE [
Max(DATE) AS MAXDATE ,
Min(DATE) AS MINDATE
group by ID;
DROP Table TEMP;
Date(MINDATE + IterNo() - 1) AS DATE
Resident TEMP1 While IterNo() <= (MAXDATE - MINDATE) + 1 ;
DROP Table TEMP1;
Could you put a sample (.qvw)?
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.
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.
Thanks. It worked.