Trouble creating new dimension

[Martijn_M]

Hi There,

I am trying to create a new dimension, so that I can show data quality issue's in my dashboard. I would like the records that are empty in the field [OGE Energie Label Empire (Empire)] to show in this dimension. The script that i was using is If([OGE Energie Label Empire (Empire)]=''as Data issue 1). I am very new so I am aware that I am missing an import piece in the script, could somebody help?

Kind regards,

Martijn

[rbartley]

Hi Martijn,

Instead of  If([OGE Energie Label Empire (Empire)]=''as Data issue, try using IsNull(), e.g.

If(IsNull([OGE Energie Label Empire (Empire)]),1,0) as "Data issue".

You can then filter the result as you like.

[Martijn_M]

Hi Rbartley,

It stops working at the first comma with this peace of script. And should i add something after this line of script to have it appear as a dimension in Qlik?

Many thanks and kind regards,

Martijn

[rbartley]

Hi Martijn,

Can you upload your app?  It will be easier to find the issue this way.

Regards,

Richard

[rbartley · Accepted solution]

In the meantime, I created a basic app with the following load script:

[Original Data]:
Load  * Inline
[
ID,OGE Energie Label Empire (Empire)
1,hfskjdjfdkjfsk
2,dsiuiudududu
3,
4,438747474484
5,
6,djdsdsdsood
];


[Data Quality]:
Load
ID,
[OGE Energie Label Empire (Empire)],
If(IsNull([OGE Energie Label Empire (Empire)]),ID) as "Data Quality Issue Dimension",
If(Len([OGE Energie Label Empire (Empire)])=0,ID) as "Data Quality Issue Dimension 2"
Resident [Original Data];

Drop Table [Original Data];

and then created one table with all fields:

and one table with just field "Data Quality Issue Dimension 2"

You can see from the first table that the isnull() function does not identify the data quality issues correctly (I assume that this is because I used load * inline), but the second method using Len() does.  So, if you only include that field in a table, it will only show the ID for those records with a data quality issue.