Generating Missing Data In QlikView

[hic]

• 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

[maksim_senin]

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

[hic]

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

[maksim_senin]

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

[#5]

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

[Marcio_Campestrini]

Hi HIC

I stopped with this and your brief helped a lot.

Thanks.

[john_novello]

This is GREAT! Thanks for putting this together.

[#8]

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;

[hic]

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

[#10]

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

[#11]

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