topic Re: Re: Generate values for all dates in QlikView

Generate values for all dates

nayrlloyd — Sat, 01 Feb 2014 02:06:44 GMT

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:

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

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

ItemNo	DateUsed	QtyUsed
30	1/1/2014	1000
30	1/2/2014	0
30	1/3/2014	1200
30	1/4/2014	1330
30	1/5/2014	0
30	1/6/2014	1469
30	1/7/2014	0
20	1/1/2014	0
20	1/2/2014	1176
20	1/3/2014	1376
20	1/4/2014	0
20	1/5/2014	1447
20	1/6/2014	0
20	1/7/2014	0

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

Anonymous — Sat, 01 Feb 2014 03:27:18 GMT

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

swuehl — Sat, 01 Feb 2014 09:29:00 GMT

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

maxgro — Sat, 01 Feb 2014 09:33:03 GMT

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

hic — Sat, 01 Feb 2014 17:55:44 GMT

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

HIC

Re: Generate values for all dates

Anonymous — Sat, 01 Feb 2014 18:52:13 GMT

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

Re: Generate values for all dates

Sat, 01 Feb 2014 19:21:40 GMT

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.

Re: Generate values for all dates

nayrlloyd — Sun, 02 Feb 2014 07:20:29 GMT

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:

ItemNo	DateUsed	UsedQty
30	1/1/2014	1000
30	1/3/2014	1200
30	1/4/2014	1330
30	1/6/2014	1469
30	1/6/2014	1469
20	1/2/2014	1176
20	1/3/2014	1376
20	1/5/2014	1447

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

Anonymous — Sun, 02 Feb 2014 08:12:17 GMT

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

swuehl — Sun, 02 Feb 2014 15:11:51 GMT

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

nayrlloyd — Mon, 03 Feb 2014 20:41:00 GMT

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

swuehl — Mon, 03 Feb 2014 21:21:28 GMT

Sure, if you don't need the detailed QtyUsed values, you can aggregate in the script.