Fill missing fields in table

[Anonymous]

How can I achieve the desired situation? By selection of column 'Season' I want to see all months for comparison with last year (in 201401 no sales, but in 201301 100 euro sales).

Current situation

Season	Company	Article number	Sales amount
201403	Italy	12345	200
201404	Italy	12345	100
201411	Italy	12345	150
201412	Italy	12345	50

Desired situation

Season	Company	Article number	Sales amount
201401	Italy	12345	0
201402	Italy	12345	0
201403	Italy	12345	200
201404	Italy	12345	100
201405	Italy	12345	0
201406	Italy	12345	0
201407	Italy	12345	0
201408	Italy	12345	0
201409	Italy	12345	0
201410	Italy	12345	0
201411	Italy	12345	150
201412	Italy	12345	50

[hemhund2016]

Hi,

You would need to create the master Calendar table and use the YearMonth as Season, which should solve your issue.

Creating A Master Calendar

Thanks

Hemanth.

[JonnyPoole]

As above, for your data set a master calendar would look like this kind of script:

Data:

LOAD Season,

     Company,

     [Article number],

     [Sales amount]

FROM

[https://community.qlik.com/thread/196528]

(html, codepage is 1252, embedded labels, table is @1);

// find out the range of years required for the data set

MinMaxSeason:

Load

  max(Season) as MaxSeason,

  min(Season) as MinSeason

Resident Data;

let vMinYear=left( peek('MinSeason',0,'MinMaxSeason'),4);

let vMaxYear=left( peek('MaxSeason',0,'MinMaxSeason'),4);

drop table MinMaxSeason;

//build a master month table with all years represented

for i = $(vMinYear) to $(vMaxYear)

  MasterCalendar:

  load

  $(i)*100+ RowNo() as Season

  AutoGenerate 12;

Next i;

[Anonymous]

Thanks for your fast reply.

When I filter on company 'Italy' in your sales amount example, Qlikview selects the records of seasons '201403, 201404, 201411 and 201412'.

With comparison of previous season '201301' with 100 euro sales will not be selected because of missing fields in 'Company' and 'Article number'. My solution was filling the missing fields in your sales amount example, but maybe there is an easier way to solve this.

Can you help me?

Previous season:

(($(i)*100) -100 + RowNo() as Season_Previous. 

[Anonymous]

Thanks! See my reply to Johanthan Poole. Will you help me too again?

[JonnyPoole]

I see.  Here is one possible solution (modified) that i resolved in the data model.

Data:

load * inline [

Article number,Company,Sales amount,Season

12345,Italy,50,201412

12345,Italy,100,201404

12345,Italy,150,201411

12345,Italy,200,201403

12345,France,20,201412

12345,France,10,201404

12345,France,30,201411

12345,France,15,201403];

Sales:

Load

  [Article number],

  [Sales amount],

  AutoNumber(Company&Season,'CompanySeasonID') as CompanySeasonID

resident Data;

// find out the range of years required for the data set

MinMaxSeason:

Load

  max(Season) as MaxSeason,

  min(Season) as MinSeason

Resident Data;

let vMinYear=left( peek('MinSeason',0,'MinMaxSeason'),4);

let vMaxYear=left( peek('MaxSeason',0,'MinMaxSeason'),4);

drop table MinMaxSeason;

Companies:

Load distinct

  Company

Resident Data; 

//build a month/company cross product table

for j= 1 to NoOfRows('Companies')

  for i = $(vMinYear) to $(vMaxYear)

   CompanyCalendarMatrix:

   load

    *,

      AutoNumber(Company&Season,'CompanySeasonID') as CompanySeasonID;

   load

   $(i)*100+ Month as Season,

   Peek('Company',$(j)-1,'Companies') as Company;

   load * inline [

   Month

   1

   2

   3

   4

   5

   6

   7

   8

   9

   10

   11

   12];

  Next i;

Next j;

drop table Data;

[#7]

Team

  how about possibility of saving the data at the QVD. I too facing the same issue. My scenario is

Sale id	date	amount
1	1/1/2012	10
2	2/2/2012	4
3	4/4/2012	5
4	5/5/2012	7

There is no sale for the 3 month of 2012, I would like to save the data at the QVD end.

Kindly suggest the solution.