Replacing null-values created through Identifier in linked table

[Sapphire]

Heya!

I have an issue with (maybe)null-values that I can´t get rid of with the solutions I found so far, so once again this forum gets a bit richer with questions about the beloved null-value.

I have:

one Table with different values. MoT might be clear for some instances and empty for others.

I need:

The empty instances should be replaced with 'Null', 'Unknown' or else, so that I can filter specifically for them and recognise them via diagrams. etc-

My Issue/Identified problems:

The table that has this empty values is the result of two tables linked with an Identifier. The table that contains the MoT has no empty or null-values. The linked table with the other information does not contain MoT but other Information. Both table have the Identifier that links them. However, the second table has more instances of the Identifier (though no duplicates). I suspect, this is the source of the empty values in the target table.

Here as an example:

Table 1 (MoT):

IDENTIFIER	Mode of Transport
ID1	Train
ID3	Ship
ID4	Train

Table 2 (Additional):

IDENTIFIER	Additional Information
ID1	Blue
ID2	Yellow
ID3	Blue
ID4	Yellow

Table 3 (Target Table):

IDENTIFIER	Mode of Transport	Additional Information
ID1	Train	Blue
ID2	-	Yellow
ID3	Ship	Blue
ID4	Train	Yellow

Due to the fact that I can't sort by these empty values I fail to get clean tables or, in consequece, clean and correct diagrams. Hence I need to replace this null-values with basically anything that is sortable, preferably a label like "unknown".

My tried solutions:

- Tried to replace null-values with if(isnull([Mode of Transport]),'Null',[Mode of Transport]) in Data Load Editor for Data Set of Table 1. --> Obviously not working, since there are no null-Values to replace.

- Tried to replace null-values with the same expression in the column of the table. --> no effect.

- Tried to replace with if([Mode of Transport]='','Null',[Mode of Transport]) --> no effect.

- Tried uttering a demonic incantation --> Room got a bit darker, else no effect.

- Tried with Alt([Mode of Transport],'N/A') --> every value changed to 'N/A'.

Now I'm a bit out of options. I'm not wure why the isnull()-solution does not work. Could it be that the "anti-value" created in my example is no null or something else? And does anyone of you have an Idea what else I can try to fix my issue?

Faithful regards,

Sapphire

[sidhiq91]

@Sapphire  Please see the code below that was used in back end script:

NoConcatenate
Temp:
Load * Inline [
IDENTIFIER, Mode of Transport
ID1, Train
ID3, Ship
ID4, Train
];

Outer join (Temp)
//Temp1:
Load * Inline [
IDENTIFIER, Additional Information
ID1, Blue
ID2, Yellow
ID3, Blue
ID4, Yellow
];

NoConcatenate
Temp1:
Load *,
EmptyIsNull([Mode of Transport]) as [New Mode of Transport]
Resident Temp;

Drop table Temp;

Exit Script;

In the Front end, use the expression as shown in the screen shot below:

IF this resolves the issue, please like and accept it as a solution.

[Sapphire]

Hello Sidhig91,

thanks for your quick reply! Sadly, since the data model I'm working with is quite complex (and I'm neither the author nor deep enough - yet - in the backend syntax of sql) I'm struggling to translate your code into mentioned data model.

As for the frontend solution: That only works as long as the backend part is implemented as well, correct? Because without it for me this expression still does not change the null-values.

Is there a frontend solution that does not require bigger backend changes? And, for my understanding: Why does said expression not work in this use case?

Faithful regards,

Sapphire

[sidhiq91]

@Sapphire  It is not a huge change in the data model just a line of script, You can follow and let me know if it worked.

[Or · Accepted solution]

Not to try and compete with demonic incantations, but perhaps you could try, as a calculated dimension:

=Coalesce(Aggr(Only([Mode of Transport]),IDENTIFIER),'No Value')

[Sapphire]

Thank you, Or!

While I had to abuse your expression a bit (figured out other errors in my whole model - would be to easy if there wee none), it now works and does what is should. Performance-wise it seems to be quite heavy, though. For that, however, I most likely would need to get the backup solution working. Nevertheless: Now everything shows the things that are supposed to be seen. So my incantations were no match for your solution 😉

Sincere regards,

Sapphire

[Sapphire]

Hello Sidhig91,

as far as I see your solution, it mainly boils down to the line "EmptyIsNull([Mode of Transport]) as [New Mode of Transport]", so I agree that in theory it should be a small change. The Issue I'm having is more of the fact, that the data model I'm using was created by someone else who put together quite a big backend script with lots of interdependencies, hence I still struggle to find out where exactly to put in your solution in a way that doesn't break it. If I'd get it to work, that would be great, performancewise. So although I already marked this issue solved (since the frontend solution does the trick, though with impact on performance),  I intend to get the backend solution to work. I'll give a notice when this happens 🙂

Sincere regards,
Sapphire