Qlik Community

QlikView Documents

Documents for QlikView related information.

Generating Missing Data In QlikView

Employee
Employee

Generating Missing Data In QlikView

  • 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

Comments
maxim_senin
Contributor III

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

Employee
Employee

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

maxim_senin
Contributor III

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

Not applicable

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

MCampestrini
Valued Contributor

Hi HIC

I stopped with this and your brief helped a lot.

Thanks.

john_novello
New Contributor II

This is GREAT! Thanks for putting this together.

Not applicable

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;

Employee
Employee

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

Not applicable

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

Not applicable

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

Version history
Revision #:
1 of 1
Last update:
‎02-04-2013 08:35 AM
Updated by:
Employee