Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

how we can replace the empty void boxes by "Not informed" ?

Do you know how we can replace the empty void boxes with "Not informed" while keeping the other values ​​of the table "Yes"?

 

Capture Qlik sense.JPG

6 Replies
Partner
Partner

Re: how we can replace the empty void boxes by "Not informed" ?

Hi,

Please look at these posts. They show you that you can use a mapping for this or a resident load with an if.

https://community.qlik.com/t5/New-to-QlikView/How-to-replace-null-values-with-another-value/td-p/836...

https://community.qlik.com/t5/QlikView-Scripting/How-to-replace-Null-by-a-string-value/td-p/455729

They will solve your problem.

Jordy

Climber

Work smarter, not harder
Partner
Partner

Re: how we can replace the empty void boxes by "Not informed" ?

Hello sir but my fields are not Null They are empty/void so how can I resolve this problem?

Partner
Partner

Re: how we can replace the empty void boxes by "Not informed" ?

Hi,

Can you show me your script? I can edit this that you won't have empty values.

Jordy
Climber

Work smarter, not harder
Partner
Partner

Re: how we can replace the empty void boxes by "Not informed" ?

It's very long I use the script from Qlik license Session Monitor so it's very long:

SUB monitor_apps_REST_app

LIB CONNECT TO 'monitor_apps_REST_app (qsvm_administrateur)';

RestConnectorMasterTable:
SQL SELECT
"id" AS "id_u4",
"createdDate" AS "createdDate_u0",
"modifiedDate" AS "modifiedDate_u0",
"modifiedByUserName" AS "modifiedByUserName_u0",
"name" AS "name_u3",
"publishTime",
"published",
"description",
"fileSize",
"lastReloadTime",
"availabilityStatus",
"__KEY_root",
(SELECT
"userId",
"userDirectory",
"__FK_owner"
FROM "owner" FK "__FK_owner"),
(SELECT
"name" AS "name_u2",
"__FK_stream"
FROM "stream" FK "__FK_stream")
FROM JSON (wrap on) "root" PK "__KEY_root";

LET NumRows_monitor_apps_REST_app = NoOfRows('RestConnectorMasterTable');

map_stream:
Mapping LOAD
[__FK_stream] AS [__KEY_root],
[name_u2] AS Stream
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_stream]);

App_Stream:
LOAD
[id_u4] AS ObjectId,
ApplyMap('map_stream',__KEY_root,'Unpublished') as [App Stream]

RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

map_app_owner:
Mapping LOAD
[__FK_owner] AS [__KEY_root],
[userDirectory] & '\' & [userId] as AppOwner
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_owner]);

App:
LOAD
[id_u4] AS ObjectId,
[id_u4] AS AppId,
date(alt(
date#(left(createdDate_u0,10),'YYYY-MM-DD'),
date#(left(createdDate_u0,10),'YYYY/MM/DD'),
date#(left(createdDate_u0,10),'MM-DD-YYYY'),
date#(left(createdDate_u0,10),'MM/DD/YYYY'),
date#(left(createdDate_u0,10),'YYYY.MM.DD'),
'No valid date')
) as [App Created Date],
date(alt(
date#(left(modifiedDate_u0,10),'YYYY-MM-DD'),
date#(left(modifiedDate_u0,10),'YYYY/MM/DD'),
date#(left(modifiedDate_u0,10),'MM-DD-YYYY'),
date#(left(modifiedDate_u0,10),'MM/DD/YYYY'),
date#(left(modifiedDate_u0,10),'YYYY.MM.DD'),
'No valid date')
) as [App Modified Date],
[modifiedByUserName_u0] AS [App Modified By],
[name_u3] AS [App Name QRS],
if(left(publishTime,4)='1753','Never',timestamp(publishTime)) AS [App Publish Time],
[published] AS [App Published],
[description] AS [App Description],
floor([fileSize]/1024) AS [App File Size], // In Kb
timestamp([lastReloadTime]) AS [App Last Reload Time],
[availabilityStatus] AS [App Availability Status],
ApplyMap('map_app_owner',__KEY_root,'Unknown Owner') AS [App Owner]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

// Unify the App Name from the Logs and from QRS (To present the most up-to-date App Name while preserving App Name history)
tempAppName:
NoConcatenate
Load
Distinct ObjectId,
ObjectId as AppIdQRS,
[App Name QRS]
Resident App;

Outer Join (tempAppName)
Load
Distinct ObjectId,
ObjectId as AppIdHistorical,
[App Name] as [App Name Historical]
Resident LogContent
Where len([App Name])>0;

AppName:
NoConcatenate
Load
ObjectId,
if(isnull([App Name QRS]),[App Name Historical],[App Name QRS]) as [App Name],
if(isnull(AppIdQRS) and index(ObjectId,'|')=0,AppIdHistorical,AppIdQRS) as AppId,
if(index(ObjectId,'|')>0,null(),isnull([App Name QRS])*-1) as [AppId Removed from QRS],
[App Name Historical]
Resident tempAppName;

Drop Table tempAppName;
Drop Field [App Name] from LogContent;
Drop Field [App Name QRS] from App;
Drop Field AppId from App;
// End unify App Name




LIB CONNECT TO 'monitor_apps_REST_app (qsvm_administrateur)';

RestConnectorMasterTable:
SQL SELECT
"id" AS "id_u3",
"createdDate" AS "createdDate_u0",
"modifiedDate" AS "modifiedDate_u0",
"modifiedByUserName" AS "modifiedByUserName_u0",
"name" AS "name_u2",
"appId",
"sourceAppId",
"targetAppId",
"publishTime",
"published",
"description",
"fileSize",
"lastReloadTime",
"thumbnail",
"savedInProductVersion",
"migrationHash",
"dynamicColor",
"availabilityStatus",
"privileges" AS "privileges_u2",
"schemaPath" AS "schemaPath_u0",
"stream",
"__KEY_root",
(SELECT
"id" AS "id_u0",
"createdDate",
"modifiedDate",
"modifiedByUserName",
"value",
"schemaPath",
"__KEY_customProperties",
"__FK_customProperties",
(SELECT
"id",
"name",
"valueType",
"privileges",
"__KEY_definition",
"__FK_definition",
(SELECT
"@Value",
"__FK_choiceValues"
FROM "choiceValues" FK "__FK_choiceValues" ArrayValueAlias "@Value")
FROM "definition" PK "__KEY_definition" FK "__FK_definition")
FROM "customProperties" PK "__KEY_customProperties" FK "__FK_customProperties"),
(SELECT
"id" AS "id_u1",
"userId",
"userDirectory",
"name" AS "name_u0",
"privileges" AS "privileges_u0",
"__FK_owner"
FROM "owner" FK "__FK_owner"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_tags"
FROM "tags" FK "__FK_tags" ArrayValueAlias "@Value_u0"),
(SELECT
"id" AS "id_u2",
"name" AS "name_u1",
"privileges" AS "privileges_u1",
"__FK_stream"
FROM "stream" FK "__FK_stream")
FROM JSON (wrap on) "root" PK "__KEY_root";

[choiceValues]:
LOAD [@Value] AS [@Value],
[__FK_choiceValues] AS [__KEY_definition]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_choiceValues]);


[definition]:
LOAD [id] AS [id],
[name] AS [name],
[valueType] AS [valueType],
[privileges] AS [privileges],
[__KEY_definition] AS [__KEY_definition],
[__FK_definition] AS [__KEY_customProperties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_definition]);


[customProperties]:
LOAD [id_u0] AS [id_u0],
[createdDate] AS [createdDate],
[modifiedDate] AS [modifiedDate],
[modifiedByUserName] AS [modifiedByUserName],
[value] AS [value],
[schemaPath] AS [schemaPath],
[__KEY_customProperties] AS [__KEY_customProperties],
[__FK_customProperties] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customProperties]);


[owner]:
LOAD [id_u1] AS [id_u1],
[userId] AS [userId],
[userDirectory] AS [userDirectory],
[name_u0] AS [name_u0],
[privileges_u0] AS [privileges_u0],
[__FK_owner] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_owner]);


[tags]:
LOAD [@Value_u0] AS [@Value_u0],
[__FK_tags] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);


[stream]:
LOAD [id_u2] AS [id_u2],
[name_u1] AS [name_u1],
[privileges_u1] AS [privileges_u1],
[__FK_stream] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_stream]);


[root]:
LOAD [id_u3] AS [id_u3],
[createdDate_u0] AS [createdDate_u0],
[modifiedDate_u0] AS [modifiedDate_u0],
[modifiedByUserName_u0] AS [modifiedByUserName_u0],
[name_u2] AS [name_u2],
[appId] AS [appId],
[sourceAppId] AS [sourceAppId],
[targetAppId] AS [targetAppId],
[publishTime] AS [publishTime],
[published] AS [published],
[description] AS [description],
[fileSize] AS [fileSize],
[lastReloadTime] AS [lastReloadTime],
[thumbnail] AS [thumbnail],
[savedInProductVersion] AS [savedInProductVersion],
[migrationHash] AS [migrationHash],
[dynamicColor] AS [dynamicColor],
[availabilityStatus] AS [availabilityStatus],
[privileges_u2] AS [privileges_u2],
[schemaPath_u0] AS [schemaPath_u0],
[stream] AS [stream],
[__KEY_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);


DROP TABLE RestConnectorMasterTable;

ENDSUB

 

 

eduardo_dimperi
Valued Contributor II

Re: how we can replace the empty void boxes by "Not informed" ?

If(len>0,YourField,'Not Informed')

Partner
Partner

Re: how we can replace the empty void boxes by "Not informed" ?

Can you highlight the fields UserAttributes, Appname and User Name in your script? Because it looks like you have renamed them.

Jordy
Climber

Work smarter, not harder