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

[Anonymous]

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

[#3]

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.

Category	Date	Order Amount
A	1/1/2017	$10
A	1/2/2017	$5
B	1/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?

Category	min(Order Amount)
A	$5
B	$0

[hic]

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]

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?

[Yoda300_]

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