Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
enzazaden
New Contributor III

Fill missing fields in table

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

SeasonCompanyArticle numberSales amount
201403Italy12345200
201404Italy12345100
201411Italy12345150
201412Italy1234550


Desired situation

SeasonCompanyArticle numberSales amount
201401Italy123450
201402Italy123450
201403Italy12345200
201404Italy12345100
201405Italy123450
201406Italy123450
201407Italy123450
201408Italy123450
201409Italy123450
201410Italy123450
201411Italy12345150
201412Italy1234550
6 Replies
hemhund2016
Contributor

Re: Fill missing fields in table

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.

Employee
Employee

Re: Fill missing fields in table

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;

Capture.PNG

Capture2.PNG

enzazaden
New Contributor III

Re: Fill missing fields in table

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. 

enzazaden
New Contributor III

Re: Fill missing fields in table

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

Employee
Employee

Re: Fill missing fields in table

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

Capture.PNGCapture2.PNG

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;

Not applicable

Re: Fill missing fields in table

Team

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

 

Sale iddateamount
11/1/201210
22/2/20124
34/4/20125
45/5/20127

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.

Community Browser