7 Replies Latest reply: Jun 24, 2016 6:01 PM by srini vas RSS

    Fill missing fields in table

    Enza Zaden

      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
        • Re: Fill missing fields in table
          Hemantha Kumar Gandrothu

          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
            Jonathan Poole

            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

              • Re: Fill missing fields in table
                Enza Zaden

                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
                    Jonathan Poole

                    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;

                      • Re: Fill missing fields in table
                        srini vas

                        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.