If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
I have a chart which uses fields from 2 tables.
The 2 tables are joined on a Report_ID field.
Table_1 contains all Report_IDs.
Table_2 doesn't not contain all Report_IDs and has a 2nd field called Report_Code.
My chart shows all Report_IDs from Table_1 and Report_Code from Table_2.
When there is not corresponding Report_Code in Table_2 my chart shows a hyphen (-) in the Report_Code column.
1. How can I change my chart so it shows a blank (nothing) instead of a hyphen in the Report_Code column?
2. I have a similar issue in another chart but in this chart I want to replace the hyphen in the Report_Code column with 'No Report Code' so the user can then filter on it.
Thanks for the reply.
Is there a property in a table that replaces (-) with Null ?
Or is this the only way and I have to add the line of code you suggested to every field I load?
In Front end, you can't select the Null values.
Instead of that, after join, use below statement to make null values as value in the script.
If(Len(Trim(Report_Code))>0, Report_Code , 'No Report Code') as Report_Code
If you want to make null values for many fields, try like as mentioned by @MadiF
Before the load statement, you can specify which field you want to make null values with any some other values. In your example,
Set NullValue = 'No Report Code';
LOAD * INLINE [
Report_IDs, Division Name
1, Fruits Divison
1, Drinks Divison
2, Vegetable Divison
2, Fruits Divison
Load * Inline
Load *, 1 as flag Resident Temp;
DROP Table Temp;
This is an app that I have inherited and I am very new to this.
I have now been given the access to actually see the code in the app and it looks like this:
When these 2 tables load and join I get some Null values in a field called [Response].
So there isn't an actual record in table 2 with the Null values in the [Response] field.
The Nulls only appear once both tables are loaded and they join together.
So at this point how can I replace the Null values in the [Response] field with the value 'No Response' ?
If you do have a solution could you please tell me exactly where to put it.
Sorry for the confusion, I was working off of what someone had told me, before actually seeing it for myself.
Hope you can help as I am really stuck.
Is that the entire script for those two tables? If so, it looks like the tables aren't actually joined but are using Qlik's associative model to link info in the two tables, so it makes sense that in the front end you see nulls, as there's no direct line to line relationship between fields in Table 1 and Table 2. If a join is your actual goal, I would perhaps join the tables as a temp table, and then resident load using null as value to address the resulting nulls from the join. Ex:
Field B (Response),
Join (TableTemp) LOAD
NullAsValue Field B (Response);
Set NullValue = 'N/a- Table 2 info'; --------note, the null value can be set to anything you want
Field B (Response),
Drop Table TableTemp;
--note also that in this example I don't use the wildcard load- I'd suggest using the actual field list for now as you're familiarizing yourself with the script and potentially making more complex transformations moving forward