Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
psk180590
Contributor III

Generate Missing Weeks

Hello All,

I have the following requirement.

My Data table looks like below

 

IDWeekSales
42949673792018/01264,31
2018/06

90,27

I need to generate another Table wich looks similar to the one below

 

IDWeekSales
42949673792018/01264,31
2018/020
2018/030
2018/040
2018/050
2018/0690,27
2018/420

So, on upto the current Calender Week.

I have a Calender Table with all the weeks. My Raw data gives me only the Weeks that have some Sales value present. I need to Insert 0 as sales where there are actually none.

Thanks

Sai.

Tags (1)
1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor II

Re: Generate Missing Weeks

Maybe like attached!

hope this helps

View solution in original post

13 Replies

Re: Generate Missing Weeks

Is that week Direct field? Or it is calculating from Date field?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vardhancse
Valued Contributor III

Re: Generate Missing Weeks

Partner
Partner

Re: Generate Missing Weeks

Hi Pakalapati,

First check are you able to convert week into date or date field in your table then calculate master calender as below thread.

Creating master calendar

Thanks,

Arvind Patil

psk180590
Contributor III

Re: Generate Missing Weeks

It is calculated from a Date field

MindaugasBacius
Valued Contributor III

Re: Generate Missing Weeks

I would suggest trying the script below:


Temp_Calendar_Range:

LOAD

    Num(Date#(Min(Period), 'YYYYMM'))as MinDate,

    Num(Date#(Max(Period), 'YYYYMM')) as MaxDate

RESIDENT [Main Data];


//--- Assign the start and end dates to variables

LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');


DROP TABLE Temp_Calendar_Range; // Cleanup


Calendar:

LOAD DISTINCT

Date(Temp_Date, 'YYYY/MM') as [Period]

;

LOAD DISTINCT

MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);




This should generate the full rage of weeks between your Min and Max weeks.

After that you need to Left Join the Calendar to your Main table.

That will provide you with Null() values in weeks you're missing.

Highlighted

Re: Generate Missing Weeks

You need Master calendar for this, You can use simpler way provided by jagan‌ using Master Calendar Generation Script

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
psk180590
Contributor III

Re: Generate Missing Weeks

Hi Anil,

I already have an Master Calender. How do i now generate the sales foe each Week or Date?

Frank_Hartmann
Honored Contributor II

Re: Generate Missing Weeks

Did you uncheck "suppress zeros" on Presentation Tab?

Have a look at the attached sample

hope this helps

psk180590
Contributor III

Re: Generate Missing Weeks

Hello Frank,

Your Solution was the closest to my problem. But, when in my case i have to Display all the weeks after selecting a Company ID.

Please, see the updated app.

Here when i select a Company ID i would still want the Table to show all the weeks.