Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
that_anonymous_guy
Contributor III
Contributor III

Is there a way to add missing date values in the script?

Hi All

I am working on a dataset where I have to deal with date fields. I have converted the date field using weekname so it gives week/year. The table is something like this:

Store Target% WeekYear Duration Planned
A 100 32/20 59 1.69
A 100 32/20 59 0
A 100 32/20 59 0
A 100 35/20 59 1.69
A 100 36/20 59 1.69
A 100 40/20 59 1.69
A 100 40/20 59 0
A 100 46/20 59 1.69
A 100 02/21 59 1.69
A 100 02/21 59 0

 

In this case, I only have planned start date and planned end date and using that I have calculated duration and I have to show this in a graph as a planned progress graph so I'm calculating planned like shown above 100/59 ie 1.69. The plan is to have WeekYear in x-axis and planned in y-axis. There is a date field which have the dates in which there was any sale and using that field I have already calculated the actual progress chart and now planned is required

This is the formula used to calculate planned progress


if(WeekYear=previous(WeekYear),0, target/planned))

The problem is that because few WeekYear fields are missing I'm not getting the correct data. For eg, if startdate is 22/20 and enddate is 31/20 then I need a continuous data of all the weeks in between so that the calculations can be done correctly.

I have tried using master calender but master calender is creating the missing dates in a new table but I need the missing fields in the same table so that the calculation can be performed correctly.

Is there a way to add new rows in the table which will have the missing WeekYears so the correct calculation can be performed or is there something that I'm missing about the master calenders. I'm pretty stuck with this for a while now and any leads would be appreciated.

Please let me know if any further clarification is required.

Thanks in advance 🙂

Labels (4)
1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

3 Replies
aguirre
Creator
Creator

I think you should have a look at Master Calendar

this way you get the missing dates or weeks

 

ex. 


Orders:
LOAD * INLINE [
OrderDate
1/1/2022
10/3/2023

];

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(OrderDate) as minDate,

max(OrderDate) as maxDate

Resident Orders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

 

taken from here: https://community.qlik.com/t5/QlikView-App-Dev/Creating-A-Master-Calendar/td-p/341286

 

 

hic
Former Employee
Former Employee

Take a look at https://community.qlik.com/t5/Design/How-to-populate-a-sparsely-populated-field/ba-p/1470637

This explains how to add missing date in a table.

that_anonymous_guy
Contributor III
Contributor III
Author

@hic Thank you...that's exactly the kind of thing that I need. In my case I have table that have multiple columns other than Rates and I am trying to use inline peek for every columns to replace the missing values but it is behaving a bit differently. I hope I'm doing the right thing...haha...