Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

Hi,

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

Jordy
Climber

Work smarter, not harder
Highlighted
Partner
Partner

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

 

 

Highlighted
Specialist II
Specialist II

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

Highlighted
Partner
Partner

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