Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pranaview
Creator III
Creator III

How to make selections on a bar chart for Null values

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. 

NullBar.PNG

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

Labels (5)
3 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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.

pranaview
Creator III
Creator III
Author

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

pranaview
Creator III
Creator III
Author

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