11 Replies Latest reply: Feb 3, 2014 4:21 PM by Stefan Wühl RSS

    Generate values for all dates

      First post so forgive the dumb question.

       

      I have data with values for for some dates, but I need to calculate a mean.  So I need to show all dates and show a zero value for the dates that have no activity.

       

      For example, this is what I have:

      ItemNoDateUsedQtyUsed
      301/1/20141000
      301/3/20141200
      301/4/20141330
      301/6/20141469
      201/2/20141176
      201/3/20141376
      201/5/20141447

      This is what I would like my script to generate (assuming I wanted all dates between 1/1/2014-1/7/2014):

      ItemNoDateUsedQtyUsed
      301/1/20141000
      301/2/20140
      301/3/20141200
      301/4/20141330
      301/5/20140
      301/6/20141469
      301/7/20140
      201/1/20140
      201/2/20141176
      201/3/20141376
      201/4/20140
      201/5/20141447
      201/6/20140
      201/7/20140

      I would like to generate this data table in the script.  So far I've been able to create a calendar file which contains all dates, but I can't quite figure out how to create a record for all dates for each ItemNo.

       

      Any help would be appreciated.

        • Re: Generate values for all dates
          Michael Gardner

          Hey Ryan,

           

          This isn't a dumb question but rather complicated.  I am completely stumped as how to do this.

           

          I found a few topics on it searching this site such as Missing values on time dimension  but hopefully someone here will post an easier solution.

           

          Best of Luck!

          • Re: Generate values for all dates
            Stefan Wühl

            You can do it like this:

             

            Set DateFormat = 'M/D/YYYY';

             

            //Load your input table

            INPUT:

            LOAD ItemNo,

                 DateUsed,

                 QtyUsed

            FROM

            [http://community.qlik.com/thread/106041]

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

             

            //Generate complete date table

            CALENDAR:

            LOAD Date(makedate(2014)+recno()-1) as DateUsed AutoGenerate 7;

             

            //Date values per ItemNo

            JOIN (CALENDAR) LOAD DISTINCT ItemNo Resident INPUT;

             

            //complete with known QtyUsed values per date and ItemNo

            LEFT JOIN (CALENDAR) LOAD * Resident INPUT;

             

            drop table INPUT;

             

            //Clean up (order, fill in zero values)

            RESULT:

            NoConcatenate LOAD

              ItemNo,

              DateUsed,

              rangesum(QtyUsed) as QtyUsed

            Resident CALENDAR order by ItemNo, DateUsed;

             

            drop table CALENDAR;

              • Re: Generate values for all dates

                First, thank you all for your replies. 

                 

                I tried swuehl's solution first just b/c it was first in the list.  It worked for my sample data.  However when I applied it to my real data set I realized that I had some duplicate (but legit) values which were only being pulled in once.

                 

                New example:

                ItemNoDateUsedUsedQty
                301/1/20141000
                301/3/20141200
                301/4/20141330
                301/6/20141469
                301/6/20141469
                201/2/20141176
                201/3/20141376
                201/5/20141447

                Notice the highlighted items. When I use swuehl's script, the table that I end up with in QV has one record for 1/6/2014 (which is what I want), but the UsedQty value is only 1469.  I need it to be twice that (2938).  I thought that the RangeSum would handle that, but I realized that the INPUT table is actually only pulling the 1469 to begin with.

                 

                I imagine that there is some small tweak that I need to make to the script, but I haven't been able to figure it out just yet.  I will also try to apply Massimo and dathu's solution - though they look very similar to swuehl's.  I will also check out the pdf which was linked by Henric.

                 

                Again, thanks for all of the replies.  If any of you can see quickly off hand the reason for my problem, feel free to let me know!

                  • Re: Generate values for all dates
                    Michael Gardner

                    Instead of a Table Box, make a straight table.  Use ItemNo and DateUsed as you dimensions and Sum(UsedQty) as your expression. 

                    • Re: Re: Generate values for all dates
                      Stefan Wühl

                      My use of the DISTINCT qualifier may prevent the duplicate records from be loaded here.

                       

                      Try maybe the attached modification.

                        • Re: Generate values for all dates

                          swuehl - I ended up using your first script example.  The DISTINCT qualifier was necessary.  Embarrassingly, the only thing that I needed to add was a GROUP BY statement on the initial data load.  Here is what I ended up with:

                           

                          INPUT:

                          LOAD

                               ItemNo,

                               DateUsed,

                               Sum(QtyUsed) as QtyUsed

                          FROM

                          C:\DataSource.xlsx

                          (ooxml, embedded labels, table is Sheet2)

                          GROUP BY ItemNo, DateUsed;

                           

                          CALENDAR:

                          LOAD Date(makedate(2014)+recno()-1) as DateUsed AutoGenerate 7;

                           

                          //Date values per ItemNo

                          JOIN (CALENDAR) LOAD Distinct ItemNo Resident INPUT;

                           

                          //DROP Table INPUT;

                           

                          //complete with known QtyUsed values per date and ItemNo

                          LEFT JOIN (CALENDAR) LOAD * Resident INPUT;

                           

                          DROP Table INPUT;

                           

                          //Clean up

                          RESULT:

                          NoConcatenate LOAD

                            ItemNo,

                            DateUsed,

                            RangeSum(QtyUsed) as QtyUsed

                          Resident CALENDAR

                          Order By ItemNo, DateUsed;

                           

                          DROP Table CALENDAR;

                           

                          Thanks again to everyone for the help!

                    • Re: Generate values for all dates
                      Massimo Grossi

                      SET ThousandSep='.';

                      SET DecimalSep=',';

                      SET MoneyThousandSep='.';

                      SET MoneyDecimalSep=',';

                      SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

                      SET TimeFormat='hh:mm:ss';

                      SET DateFormat='MM/DD/YYYY';

                      SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

                      SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

                      SET DayNames='lun;mar;mer;gio;ven;sab;dom';

                       

                      // generate date

                      Let vMinDate = num('1/1/2014');

                      Let vMaxDate = num('1/7/2014');

                      TRACE $(vMinDate) TRACE $(vMaxDate);

                      Date:

                      Load Date(recno()+$(vMinDate)-1) as DateUsed

                      Autogenerate vMaxDate - vMinDate ;

                       

                      // source data

                      Source:

                      LOAD * INLINE [

                      ItemNo, DateUsed, QtyUsed

                      30, 1/1/2014, 1000

                      30, 1/3/2014, 1200

                      30, 1/4/2014, 1330

                      30, 1/6/2014, 1469

                      20, 1/2/2014, 1176

                      20, 1/3/2014, 1376

                      20, 1/5/2014, 1447

                      ];

                       

                      // cartesian ItemNo x Date

                      Tmp: load Distinct ItemNo Resident Source;

                      join (Date) load ItemNo Resident Tmp;

                      DROP Table Tmp;

                       

                      // join to add QtyUsed

                      Left join (Date) load ItemNo, DateUsed, QtyUsed resident Source;

                      DROP Table Source;

                       

                      // end

                      Table:

                      noconcatenate load ItemNo, DateUsed, alt(QtyUsed,0) as QtyUsed Resident Date;

                      DROP Table Date;

                       

                      exit script;

                      • Re: Generate values for all dates
                        Henric Cronström

                        How to do this is described in Generating Missing Data In QlikView

                         

                        HIC

                        • Re: Generate values for all dates
                          Srikanth P

                          Hi Ryan, Try like below:

                           

                          Temp:

                          LOAD * Inline [

                          ItemNo, DateUsed, QtyUsed

                          30, 1/1/2014, 1000

                          30, 1/3/2014, 1200

                          30, 1/4/2014, 1330

                          30, 1/6/2014, 1469

                          20, 1/2/2014, 1176

                          20, 1/3/2014, 1376

                          20, 1/5/2014, 1447

                          ];

                           

                          Join (Temp)

                          LOAD ItemNo , MinDate + IterNo() - 1 AS DateUsed While IterNo() <= MaxDate - MinDate +1 ;

                          LOAD ItemNo , Min(DateUsed) AS MinDate , Max(DateUsed) As MaxDate Resident Temp Group By ItemNo;

                           

                          FINAL:

                          NoConcatenate

                          LOAD ItemNo ,

                            DateUsed ,

                            RangeSum(QtyUsed) AS QtyUsed

                          Resident Temp ;

                           

                          Drop Table Temp;

                           

                          Please find the attached file for reference.