topic Re: Generate Missing Weeks in QlikView

Generate Missing Weeks

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have the following requirement.

My Data table looks like below

ID	Week	Sales
4294967379	2018/01	264,31
4294967379	2018/06	90,27

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

ID	Week	Sales
4294967379	2018/01	264,31
	2018/02	0
	2018/03	0
	2018/04	0
	2018/05	0
	2018/06	90,27

2018/42

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.

Re: Generate Missing Weeks

Anil_Babu_Samineni — Wed, 17 Oct 2018 03:28:18 GMT

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

Re: Generate Missing Weeks

vardhancse — Wed, 17 Oct 2018 04:17:01 GMT

How to generate weeks between two dates?

Re: Generate Missing Weeks

arvind_patil — Wed, 17 Oct 2018 04:39:21 GMT

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

Re: Generate Missing Weeks

psk180590 — Wed, 17 Oct 2018 06:17:11 GMT

It is calculated from a Date field

Re: Generate Missing Weeks

MindaugasBacius — Wed, 17 Oct 2018 06:29:49 GMT

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.

Re: Generate Missing Weeks

Anil_Babu_Samineni — Wed, 17 Oct 2018 08:27:41 GMT

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

Re: Generate Missing Weeks

psk180590 — Wed, 17 Oct 2018 12:13:39 GMT

Hi Anil,

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

Re: Generate Missing Weeks

Frank_Hartmann — Wed, 17 Oct 2018 12:40:44 GMT

Did you uncheck "suppress zeros" on Presentation Tab?

Have a look at the attached sample

hope this helps

Re: Generate Missing Weeks

psk180590 — Thu, 18 Oct 2018 11:39:57 GMT

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.

Re: Generate Missing Weeks

Frank_Hartmann — Thu, 18 Oct 2018 12:25:54 GMT

Maybe like attached!

hope this helps

Re: Generate Missing Weeks

psk180590 — Thu, 18 Oct 2018 13:48:57 GMT

Perfect.

Vielen Dank!!

Re: Generate Missing Weeks

Frank_Hartmann — Thu, 18 Oct 2018 14:23:29 GMT

if your requirement is resolved now please close this thread by marking correct and helpful answers

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Generate Missing Weeks

psk180590 — Thu, 18 Oct 2018 14:33:21 GMT

Already marked your suggestion as correct