generating missing months with zero sales for full year

[Anonymous]

I have a requirement to generate records for missing months. The missing records should be populated  with 0 Sales.

The link - generating missing months is very similar to what I need but I couldn't figure out how to use iterno() for my situation. I need to fill the amount for the full Year. Eg:

Source Data:

Acnt	Month	Sales
A1	Jan	56
A1	Feb	48
A1	Nov	49
A2	Jan	45
A2	Feb	34
A2	Mar	23
A2	Apr	32
A2	May	46
A2	Jun	78
A2	Jul	35
A2	Aug	54
A2	Sep	97
A2	Oct	66
A2	Nov	77
A2	Dec	88
A2	Jan	70

This is how  my resulting data set should look like:

Acnt	Month	Sales
A1	Jan	56
A1	Feb	48
A1	Nov	49
A1	Mar	0
A1	Apr	0
A1	May	0
A1	Jun	0
A1	Jul	0
A1	Aug	0
A1	Sep	0
A1	Oct	0
A1	Dec	0
A2	Jan	45
A2	Feb	34
A2	Mar	23
A2	Apr	32
A2	May	46
A2	Jun	78
A2	Jul	35
A2	Aug	54
A2	Sep	97
A2	Oct	66
A2	Nov	77
A2	Dec	88
A2	Jan	70

Appreciate all your help.

Thanks

Samy

[Anil_Babu_Samineni]

Are you expecting in script / Front end?

[Anonymous]

in the script,, thank you!

[Anil_Babu_Samineni]

Have you seen this?

Generating Missing Data In QlikView

[Anonymous]

Hi

Used following script to generate Missing Month against its values.

Temp_Date_Range:

LOAD

num(Min(DATE("RegistrationDate"))) as MinDate,

num(Max(DATE("RegistrationDate"))) as MaxDate

Resident  SourceData_TB;

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

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

drop table  Temp_Date_Range;

MasterCalender:

LOAD 

Date(Temp_Date) as "Key",

Year(Temp_Date) as Year,

Month(Temp_Date) as Month;

LOAD DISTINCT

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

  AutoGenerate(1)

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

Regards

Sahadev

[Anil_Babu_Samineni]

Can you share the QVW file where you working?