Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nayrlloyd
New Contributor II

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.

1 Solution

Accepted Solutions
nayrlloyd
New Contributor II

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!

View solution in original post

11 Replies
michael_gardner
Contributor III

Re: Generate values for all dates

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!

MVP
MVP

Re: Generate values for all dates

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;

MVP
MVP

Re: Generate values for all dates

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

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

HIC

michael_gardner
Contributor III

Re: Generate values for all dates

It similar to "Example: Populating a table with warehouse balances" within the document correct?

Not applicable

Re: Generate values for all dates

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.

nayrlloyd
New Contributor II

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!

michael_gardner
Contributor III

Re: Generate values for all dates

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

MVP
MVP

Re: Re: Generate values for all dates

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

Try maybe the attached modification.