Qlik Community

Ask a Question

Documents

QlikView documentation and resources.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE

Generating Missing Data In QlikView

Henric_Cronström

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

Attachments
Comments
sanjyotpatkar
Creator III
Creator III

Hello Henric

I am using a combination of Canonical Calendar and Generating missing reference dates concept from your previously written blogs.

we have multiple systems migrated into one new system and I need to show all dates on one time axis.

eg. [Opened Date], [Resolution Date] from New system

[Date Raised],[Resolved Date] from TR System

[Open date UTC],[Resolve date UTC] from IN system

I need to show a trend of outstanding tickets across one common month axis.

Outstanding ticket count as of 28 Feb = all tickets with blank [Resolution Date],[Resolved Date] and [Resolve date UTC] while being opened in Feb or prior to Feb. +

Tickets that were opened in Feb or prior to Feb but resolved after 28 Feb.

From the previous blogs on generating reference dates I am able to generate missing dates but only for one date field.

Challenge that I am facing is that even if I generate missing dates for each of these date fields how do i show it all on one axis.

Please can you suggest how do I go about it

0 Likes
Not applicable

Hi All,

Here's my scenario. I have a transaction file grouped by category and date, but not all categories happen every day. When I calculate minimum number of transactions, qlikview is not considering the absent numbers as 0. Below is an example data.

CategoryDateOrder Amount

A

1/1/2017$10
A1/2/2017$5
B1/2/2017$5

I want to return something like below. For Category A, return minimum of order amount for these 2 days, which will be $5. Same for Category B. Since there is no transaction on 1/1, it should consider it as $0 and therefore the minimum is $0. How can I do this?

Categorymin(Order Amount)
A$5
B$0
0 Likes
Henric_Cronström

One way is the following:

Data:
Load
Category,
Date#(Date,'M/D/YYYY') as Date,
Keepchar([Order Amount],'0123456789.') as [Order Amount]
Inline
[Category,Date,Order Amount
A,1/1/2017,$10
A,1/2/2017,$5
B,1/2/2017,$5]
;

Tmp:
Load distinct Category Resident Data;
Join
Load distinct Date Resident Data;
Join
Load * Resident Data;

DataAllCombinations:
NoConcatenate
Load Category, Date, RangeSum([Order Amount]) as [Order Amount] Resident Tmp;

Drop Table Data, Tmp;

madnanansari
Creator
Creator

I have an Employee Table from HR where Only below information is available:

Employee ID

Date

Status (On Job / Off Job)

If the status is 'On Job' and the date is '01-JAN-2017' and this is the last record in the table; this means today also he is ON JOB. So I generated the missing record as explained by you in the table till today.

Now if the status is 'Off Job' for whatever be the reason (Retired, Terminated, Resigned ext. )  and the date is '01-JAN-2017' and this is the last record in the table for this employee; I don't need to generate any further record beyond this date for this employee since he is no more the employee.

By the default mechanism defined in the PDF, the script will generate 'Off Job' record for this employee from '01-JAN-2017' till today which is not required. so either I delete those records or don't generate any further records.

Can u guess how this can be achieved?

0 Likes
Yoda300_
Contributor
Contributor

Hello HIC. thanks for your document. Im a beginner with Qlik and I got little Issue.

used this formula in the load, wanted to say, if there is no value in column A, to put 'Externe" in column B, else A value

if(IsNull(A),'Externe',A) as B

How do I solve it ? thank you

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