Remove Hyphens from chart

[Gazza]

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

G 

[MadiF]

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

[Gazza]

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;

[Gazza]

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]

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