Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Gazza
Contributor II
Contributor II

Remove Hyphens from chart

Hi,

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.

 

Thank you

Labels (1)
  • Other

5 Replies
MadiF
Contributor III
Contributor III

@Gazza Have you tried a NullAsValue solution on the script side? https://help.qlik.com NullAsValue 

Gazza
Contributor II
Contributor II
Author

Hi Madi,

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?

thanks

G

MayilVahanan

Hi @Gazza 

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,

NullAsValue ReportCode;

Set NullValue = 'No Report Code';

Temp:
LOAD * INLINE [
Report_IDs, Division Name
1, Fruits Divison
1, Drinks Divison
2, Vegetable Divison
2, Fruits Divison
];

Join
Load * Inline
[
Report_IDs, ReportCode
1, nn
];

Final:

Load *, 1 as flag Resident Temp;

DROP Table Temp;

Thanks & Regards,
Mayil Vahanan R
Gazza
Contributor II
Contributor II
Author

Hi,

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:

 

Table1:
LOAD *
FROM [../QVDs/10_ItemCharge.qvd]
(qvd);

Table2:
LOAD *
FROM [../QVDs/10_ChargeSuccess.qvd]

 

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.

 

thank you

G

MadiF
Contributor III
Contributor III

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:

 

TableTemp:
LOAD

Field A,

Field B (Response),

Field C

FROM [../QVDs/10_ItemCharge.qvd]
(qvd);

Join (TableTemp) LOAD 

Field A,

Field Q,

Field Y

FROM [../QVDs/10_ChargeSuccess.qvd]

 

NullAsValue Field B (Response);

Set NullValue = 'N/a- Table 2 info';          --------note, the null value can be set to anything you want

FinalTable:

Load

Field A,

Field B (Response),

Field C,

Field Q,

Field Y

Resident TableTemp;

 

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