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.
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.
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...
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:
.png "Miguel_Angel_Baeyens"){kind=avatar}
