topic Re: Fill missing fields in table in QlikView

Fill missing fields in table

Anonymous — Mon, 14 Dec 2015 16:26:25 GMT

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

Re: Fill missing fields in table

hemhund2016 — Mon, 14 Dec 2015 16:47:39 GMT

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.

Re: Fill missing fields in table

JonnyPoole — Mon, 14 Dec 2015 17:07:07 GMT

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;

Re: Fill missing fields in table

Anonymous — Thu, 17 Dec 2015 09:51:52 GMT

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.

Re: Fill missing fields in table

Anonymous — Thu, 17 Dec 2015 09:54:02 GMT

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

Re: Fill missing fields in table

JonnyPoole — Thu, 17 Dec 2015 17:34:05 GMT

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

12];

Next i;

Next j;

drop table Data;

Re: Fill missing fields in table

Fri, 24 Jun 2016 22:01:57 GMT

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.