article Generating Missing Data In QlikView in Member Articles

Generating Missing Data In QlikView

hic — Mon, 04 Feb 2013 13:35:25 GMT

What techniques are there to generate “new” tables; tables that don’t exist in the source data?
How do I loop over one and the same record in source data?
How do I propagate a value from above record to the current one?
How do I populate a sparsely populated field?
How do I generate all combinations – the Cartesian product – of two or more fields?
How do I make simulations in QlikView?

These questions and others are answered in this Technical Brief.

HIC

Re: Generating Missing Data In QlikView

maksim_senin — Wed, 06 Feb 2013 01:18:35 GMT

Hi Henric,

This is must-to-read paper for all beginners, it's sad I had not one 1+ year ago! Thank you!

Please find my two cents, this is what personally I would add to the document:

1. Master Calendar - you only mention dates but for some tasks it's necessary to have time as well, i.e. either hours, minutes, seconds or some combinations ofthem (for instance, in retail we need to know average number of a store visitor per hour or even per each 15 minutes). That would be a good thing to make it clear to a reader.

2. Also I'd mention Previous() function which can be used in a condition over values of previous row of a source, whilst Peek() allows to do the same but over values of previous row already loaded into the associative database of QV. One reason to use Previous() is a necessity to build a condition over values which are not to be loaded into associative database but still are meaningful for algorithm.

3. Pages 12 and 13, step "B" - you use vMinDate and vMaxDate variables which are not defined, it's better to make a reference to page 7 for the definition.

The document in a whole is good enough even without all of these, though.

Best regards,

Maxim

Re: Generating Missing Data In QlikView

hic — Wed, 06 Feb 2013 07:05:40 GMT

Glad you like it, and thanks for the feedback. I will gather some more feedback and then revise the document in a some weeks.

HIC

Re: Generating Missing Data In QlikView

maksim_senin — Thu, 07 Feb 2013 05:29:59 GMT

Hi Henric,

Updating of the docuemnt is up to you since the document does describe how to "generate missing data in QlikView" and it's not intended to describe modeling in a whole.

In case you start adding some additions into a specialized paper you risk to start describing The Universe

Best regards,

Maxim

Re: Generating Missing Data In QlikView

Sat, 30 Nov 2013 18:33:36 GMT

That saved me a night shift on a saturday evening. Exactly what I needed. Great stuff. Thanks a lot 😉

Re: Generating Missing Data In QlikView

Marcio_Campestrini — Mon, 16 Jun 2014 19:52:33 GMT

Hi HIC

I stopped with this and your brief helped a lot.

Thanks.

Re: Generating Missing Data In QlikView

john_novello — Tue, 02 Sep 2014 17:57:35 GMT

This is GREAT! Thanks for putting this together.

Re: Generating Missing Data In QlikView

Tue, 26 May 2015 16:29:25 GMT

I tried to replace NULL values in der field 'Wert" with the value of the above record and import the new values in the field 'wertneu' in the table 'result'. But now in 'wertneu' there is the same as in the 'wert'. Where is the mistake? I would be very happy, if somebody can help me. Thank you!

TransactionTable:

load * inline

[Date,FB,Wert

01.01.2014,AUGE, 1

15.01.2014,CHIE, 15

31.01.2014,CHIP, 31];

MinMaxDate:

Load Min(Date) as MinDate, Max(Date) as MaxDate resident TransactionTable;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate')-1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

Drop Table MinMaxDate;

MasterCalendar:

Load Date,

Year(Date) as Year,

Month (Date) as Month,

Day(Date) as Day;

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate-vMinDate;

Result:

LOAD Date, FB, Wert,

if(isnull(Wert),Peek(Wert),Wert)as Wertneu resident TransactionTable

order by Date, FB, Wert;

Re: Generating Missing Data In QlikView

hic — Tue, 26 May 2015 18:06:29 GMT

You need to join the dates with the transaction table so that you "generate" all missing dates. See How to populate a sparsely populated field.

Further, you should probably use the following definition of Wertneu:

If(isnull(Wert),Peek(Wertneu),Wert)as Wertneu

otherwise you will just get a value on the row below, and not on all empty rows.

HIC

Re: Generating Missing Data In QlikView

Wed, 27 May 2015 12:35:17 GMT

Wonderful! Thank you very much, Henric Cronström! I have solved the problem.

Re: Generating Missing Data In QlikView

Thu, 21 Jan 2016 00:18:18 GMT

Thanks heaps for this document. It succinctly defines the challenges, the use cases, and the solutions.

Re: Generating Missing Data In QlikView

Fri, 29 Jan 2016 19:14:20 GMT

Excellent article, thank you very much for taking the time to put this together. I was hoping there was an example of what I am trying to achieve, and it may be there but just not specifically. My situation is that we have a return on investment calculation for R&D projects with forecast sales in terms of whole fiscal years. Post launch, we want to look at this as a monthly revenue achievement vs. forecast so I'm assuming I will need to prorate the annual forecast value into a monthly figure for comparison. Obviously this data doesn't exist as there is only one value per FY. To further complicate the issue, each project will have a different launch date and a difference life cycle projection. Projects can launch at any point in the year and have anywhere from 5 to 15 years of projected revenue. How would you approach this problem?

Re: Generating Missing Data In QlikView

hic — Fri, 05 Feb 2016 15:12:15 GMT

Forecasting is a complicated problem, and I am not sure that generating data in the QlikView script is the right way.

It's possible, but it will be complex, and perhaps still not solve all your requirements. For instance: How many dimensions do you have? (e.g. Calendar, Products, Customers, Business units, Market campaigns, Projects, etc.) And do you want to be able to use all these dimensions? If so, you may need to generate all combinations - the Cartesian product - of all of them. This will be complex: You need to estimate the YoY increase per product, per unit and per region separately. Also, the data amount will become large.

If budgeting and forecasting is important, it may be a good idea to look at dedicated tools for this.

HIC

Re: Generating Missing Data In QlikView

Thu, 11 Feb 2016 02:25:01 GMT

Its very nice document.

Re: Generating Missing Data In QlikView

chrisg — Thu, 11 Feb 2016 11:05:34 GMT

very usefull!

Re: Generating Missing Data In QlikView

Tue, 07 Jun 2016 13:08:28 GMT

Very well explained.

Helped me a lot!!!

Thanks

Re: Generating Missing Data In QlikView

Thu, 29 Sep 2016 17:11:40 GMT

very helpful

Re: Generating Missing Data In QlikView

Anonymous — Wed, 04 Jan 2017 11:15:09 GMT

Hi Henric

I have used a combination of Canonical Calendar script and 'Generating Missing Dates in Qlikview' to create 2 different calendars as I need to show missing ticket count for the dates that don't exist.

As a result some of the graphs take almost 10 seconds to load. Users will not wait that long. Most of the formulas are written in the Expressions so I tried loading these formulae through a spreadsheet just in case it tries to improve performance. Can you please suggest what I may try to do to improve the performance? I haven't used set analysis in the expressions. These are more based on if clauses.. Not sure if this impact the performance for the charts..P

Re: Generating Missing Data In QlikView

ahaahaaha — Thu, 05 Jan 2017 07:47:59 GMT

Thank you. A good document.

Re: Generating Missing Data In QlikView

bullish35 — Thu, 05 Jan 2017 23:51:25 GMT

There may be a variety of factors impacting your performance, but as a general rule replace your IF statements with set analysis. Also recommend using flags wherever possible (1,0) . Good luck!