Filter on null values

thomsob2u — Sat, 27 Apr 2019 15:50:16 GMT

Hello all

I'm very new to QlikSense and am trying to do what I think should be a simple operation but am having a few issues...

I have a large JSON blob I am pulling from a REST service that gives me a list of applications and a subsection that provides the associated servers and operating systems. I want to expose this via a Qlik dashboard so users can filter out apps based on various criteria, including the OS type(s). The issue I bump into is how does a user filter out all apps that have a specific OS association, as I cannot filter against null values.

i.e. show all apps where 'IBM' is null should show only #1 & #5

AppId	AppName	IBM	RHEL	Windows
1	Safehaven	-	-	Y
2	RabbitWarren	Y	Y	-
3	Aztec	Y	-	-
4	Walkers	Y	Y	-
5	QuoVardis	-	Y	-

I think the right thing to do is to force an 'N' where I have a null, but I'm not sure what the most efficient approach is to do this (I have a few thousand rows and more OS's than shown ), so any suggestions or examples would be welcome.

Below is a simplified view of the structure and how I create the above.

TableA:
LOAD * INLINE [
AppId, AppName
1, Safehaven
2, RabbitWarren
3, Aztec
4, Walkers
5, QuoVardis
];

TableB:
LOAD * INLINE [
AppId, Platform, Hostname
1, Windows, server1
1, Windows, server2
1, Windows, server3
2, RHEL, server4
2, RHEL, server5
2, RHEL, server6
2, IBM, server7
3, IBM, server8
3, IBM, server9
4, IBM, server10
4, RHEL7, server11
5, RHEL7, server12
5, RHEL7, server13
];

temp1:
generic load AppId, Platform, 'Y' AS PlatformUsed resident TableB;

result:
load distinct AppId resident TableA;

FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'temp1.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (result) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i

drop table TableList;

Thanks all.

topic Filter on null values in App Development

Filter on null values