Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Do you know how we can replace the empty void boxes with "Not informed" while keeping the other values of the table "Yes"?
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/QlikView-Scripting/How-to-replace-Null-by-a-string-value/td-p/455729
They will solve your problem.
Jordy
Climber
Hello sir but my fields are not Null They are empty/void so how can I resolve this problem?
Hi,
Can you show me your script? I can edit this that you won't have empty values.
Jordy
Climber
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
If(len>0,YourField,'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