Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
So I have two tables which are associated with an ID i.e. through Qlik's associative property, i didn't use any sort of join myself because there are duplicate records in both the tables i.e ID is not a unique column and it has to be that way only, we have to load the all the values without removing the duplicates.
Now, there's a report for analysis which is Primarily based on Table 1 columns i.e. Dimensions and expressions are using fields from the Table 1 but in one chart, i have to use the dimensions from the Table 2 but keeping the expression same i.e. using the fields from Table 1 and the Count is being done on ID for the charts.
So for this chart, i am getting the counts for the IDs that are available in both Table 1 and Table 2 and getting Null where the ID is present in Table 1 but not in Table 2 and I have just renamed the null values as 'N/A' in the chart but the problem here is that user can't filter on the N/A bar to see which IDs from Table 1 are responsible for N/A values i.e. ID's missing in Table 2.
Is there any way I make this bar available for selection or do i need to make some changes in my script?
Any suggestion would be appreciated as this requirement is quite an important one from user's perspective.
Thanks
Pranav
Hello Pranaview,
You can only resolve this while loading your data (script). Below are two options, I'd recommend the first.
You can try to do one of the following:
1. (Simple) You can use a Mapping -> ApplyMap() function to bring in the desired Attribute field to the original table.
Should work like:
MapDimensionToTable:
MAPPING LOAD DISTINCT
IDField,
DesiredAttribute
FROM SourceThatContainsTheAttributeWithNulls.qvd (qvd)
;
TableWithFactData:
Load *,
ApplyMap('MapDimensionToTable', IDField, 'N/A') as DesiredAttribute
From OriginalFactSource.qvd (qvd)
;
This will bring in those attributes and populate the Null values with 'N/A' selectable value.
2. (A bit more complex) You can use Exist to 'Patch' the dimensional source with that missing gap of information.
Should go something like:
DimensionalSource:
Load DISTINCT
FieldID,
DesiredAttribute
From DimensionalSource.qvd (qvd)
;
CONCATENATE(DimensionalSource)
LOAD DISTINCT
FieldID,
'N/A' as DesiredAttribute
From OriginalFactSource.qvd (qvd)
WHERE NOT EXISTS(FieldID)
;
I hope that helps!
Regards,
S.T.
Hi Stoyan,
Thanks for your response.
I can't use the ApplyMap() function as the IDs are not unique but I think the second option should work for me without using the Distinct Load, although it will significantly increase the number of rows in the Table 2. I'll check the feasibility of this solution and get back to you soon.
Thanks for the help!
Pranav
Hi Stoyan,
Sorry for the late reply but the second option doesn't seem to be working. After doing the concatenation,I am not getting any IDs from the OriginalFactSource, hence no N/A in the Dimensional source table.
Could you please look in it?
Thanks,
Pranav