topic Need Help in removing the Nulls from the chart in QlikView

Need Help in removing the Nulls from the chart

Anonymous — Mon, 21 Mar 2011 18:24:54 GMT

Here is my file , could anybody please help me in removing the Nulls from the chart ,

https://www.yousendit.com/download/eUREaUNIQzNZY1EwTVE9PQ

Thank you ...

Need Help in removing the Nulls from the chart

Miguel_Angel_Baeyens — Mon, 21 Mar 2011 19:01:06 GMT

Hello,

It depends widely on yoru datamodel, but you can try selecting each dimension in the chart, and checking "Supress When Value is Null". It will not aggregate values other than the ones that match dimensions and expressions, but it may return unexpected results.

Hope that helps.

Need Help in removing the Nulls from the chart

Anonymous — Mon, 21 Mar 2011 19:50:45 GMT

Yes i get unexpected results when i supress the values , i have some synthetic keys in my data model so you that is causing this?

I have heard about having a link table to remove synthetic , could you please point me to app sample app where the link table is used.

Please Help...

Need Help in removing the Nulls from the chart

Miguel_Angel_Baeyens — Mon, 21 Mar 2011 21:31:03 GMT

Hi,

Synthetic keys can be replaced by the fields they store. So for example, if you have a table Invoices: InvoiceID, CustomerID, RepID, Amount and a table Customers: CustomerID, RepID, Address, and you don't rename or chagne any field and load them as they are, you will have both tables associated by two fields: CustomerID and RepID. This is ok if there is an actual correspondence between them, and data will be shown correctly.

But since QlikView takes only the name of the field to associate one table to another, you may want to rename fields or create your own link fields so you will always know how two tables are associated. In the example above, creating a CustomerID & RepID as Customer_Key (for example) in both Invoices and Customers tables.

Hope that helps.

Need Help in removing the Nulls from the chart

Anonymous — Tue, 22 Mar 2011 16:13:17 GMT

How do i combine 2 fields , using concat? but that changes the values right ?

for eg: customer id is 1234

Rep id is 123 if i concatenate both the fields how can i have filter on customer id if i want to?

Need Help in removing the Nulls from the chart

llauses243 — Tue, 22 Mar 2011 16:49:43 GMT

Hi,

This is my offer ... i does definied variable vPerformance...

Good luck, Luis

Need Help in removing the Nulls from the chart

Miguel_Angel_Baeyens — Tue, 22 Mar 2011 16:52:44 GMT

Hello,

Check the following example

Sales:LOAD CustomerID, RepID, CustomerID & '/' & RepID AS Key Date, AmountFROM Sales.qvd (qvd); Customers:LOAD CustomerID & '/' & RepID AS Key, CustomerCode & ' - ' & CustomerID AS CustCodeName, AddressFROM Customers.qvd (qvd);

Hope that helps.

Need Help in removing the Nulls from the chart

Anonymous — Tue, 22 Mar 2011 16:57:43 GMT

Sorry i did'nt get you , yes i have performance factor variable , is there something wrong with it ?

Need Help in removing the Nulls from the chart

Anonymous — Tue, 22 Mar 2011 17:46:02 GMT

Here is my script:

PeripheralAvailable:
LOAD

PERIPHERAL,
PERIPHERAL_CATEGORY,
PERIPHERAL_LOCATION,
DAY_DATE AS DAY_DATE_PERIPHERAL,
HOUR_SEG_PRIME AS TIME_SEGMENT_PERIPHERAL,
AVAILABLE_HOURS AS PERIPHERAL_AVAILABLE_HOURS

FROM
[\\qlikview\PDS Data\QCT Analytics\PeripheralAvailable.QVD]
(qvd);

PeripheralUsedHours:
LOAD

PERIPHERAL_NAME AS PERIPHERAL,
PERIPHERAL_CATEGORY,

PERIPHERAL_LOCATION,

DAY_DATE AS DAY_DATE_PERIPHERAL,

HOUR_SEG_PRIME AS TIME_SEGMENT_PERIPHERAL,
PERIPHERAL_USED_HOURS,
FROM
[\\QLIKVIEW\PDS Data\QCT Analytics\PeripheralUsedHours.QVD]
(qvd);

I need to have filters on all synthetic key values , for example , DAY_DATE_PERIPHERAL , PERIPHERAL_CATEGORY,PERIPHERAL_LOCATION etc,

In the above example assume that i need filters on CustomerID,Repid,Date,CustomerCode.IF i have filter on customerid from table Sales i dont get address from the customer table...

Need Help in removing the Nulls from the chart

Miguel_Angel_Baeyens — Tue, 22 Mar 2011 21:19:34 GMT

Hello,

Taking a look at your script, what I'd do is to concatenate both tables, since they seem they have very similar information, to create a fact table and a master calendar depending on the date field:

Peripheral:LOAD PERIPHERAL, PERIPHERAL_CATEGORY, PERIPHERAL_LOCATION, DAY_DATE AS DAY_DATE_PERIPHERAL, HOUR_SEG_PRIME AS TIME_SEGMENT_PERIPHERAL, AVAILABLE_HOURS AS PERIPHERAL_HOURS, 'Available' AS SOURCE // to be used further in set analysis and expressionsFROM[\\qlikview\PDS Data\QCT Analytics\PeripheralAvailable.QVD](qvd);CONCATENATE LOAD PERIPHERAL_NAME AS PERIPHERAL, PERIPHERAL_CATEGORY, PERIPHERAL_LOCATION, DAY_DATE AS DAY_DATE_PERIPHERAL, HOUR_SEG_PRIME AS TIME_SEGMENT_PERIPHERAL, PERIPHERAL_USED_HOURS AS PERIPHERAL_HOURS, 'Used' AS SOURCEFROM[\\QLIKVIEW\PDS Data\QCT Analytics\PeripheralUsedHours.QVD](qvd); DateMinMax:LOAD Min(DAY_DATE_PERIPHERAL) AS MIN_DATE, Max(DAY_DATE_PERIPHERAL) AS MAX_DATERESIDENT Peripheral; LET vDateMin = FieldValue('MIN_DATE', 1);LET vDateMax = FieldValue('MAX_DATE', 1);// rest of script here

Now you can compare used against available, for example with the following expression:

Sum({< SOURCE = {'Used'} >} PERIPHERAL_HOURS) / Sum({< SOURCE = {'Available'} >} PERIPHERAL_HOURS)

If you need to create timestamps for your calculations, take a look at this code.

Hope that helps.