Sometimes the easiest things get you out of really big jams ... today a simple +1 did the trick.
I'm analyzing data from Qlik Community traffic and needed to determine which product area the event was for. As you know there are lots of different forums. "New to Qlik Sense", "Qlik Sense App Development" etc.
Veteran #qlikkies will say "no problem just use the WildMatch to find all variations of Qlik Sense." That works wonders. In my case I have a whole list to find:
WildMatch("Board Title",'*Qlik Sense*', '*QlikView*', '*Qlik NPrinting*', '*Qlik Alerting*', '*Qlik Sense Mobile*', '*Qlik GeoAnalytics*', '*Qlik Connectors*', '*Qlik Server Side Extensions*', '*Qlik Catalog*', '*Qlik Compose for Data Lakes*', '*Qlik Compose for Data Warehouses*', '*Qlik Enterprise Manager*', '*Gold Client*', '*Qlik Replicate*', '*Blendr*')
Qlik rocked that and returns 1, 2, 3, 4 etc depending on whether it finds any variation of Qlik Sense, QlikView, Qlik NPrinting, Qlik Alerting etc.
So what's the problem? I need to assign a hierarchy to the events so I know if the row is for Data Analytics or Data Integration.
Again #qlikkies will tell me "Just use the Pick function to assign the appropriate value based on the index from the Wildmatch.
Pick(Wildmatc(........) , Value1, Value2, Value3 etc)
That worked fantastic for me except for 1 thing ... the rows where the field "Board Title" don't match anything. In those cases WildMatch returns a 0. Yikes the Pick function starts at index of 1.
That's where a +1 comes in handy. I can tell Qlik to increment the WildMatch values by 1, so that it's 0'th value (unfound) returns a 1 instead. Then in my Pick function I simply assign the value for the "unfound" that I would like to use.
Pick(
WildMatch("Board Title", '*Qlik Sense*', '*QlikView*', '*Qlik NPrinting*', '*Qlik Alerting*', '*Qlik Sense Mobile*', '*Qlik GeoAnalytics*', '*Qlik Connectors*', '*Qlik Server Side Extensions*', '*Qlik Catalog*', '*Qlik Compose for Data Lakes*', '*Qlik Compose for Data Warehouses*', '*Qlik Enterprise Manager*', '*Gold Client*', '*Qlik Replicate*', '*Blendr*') +1
, 0, 100, 101, 102, 103, 104, 105, 106, 107, 200, 201, 202, 203, 204, 205, 206) as [Node ID],
I've found that the beauty of Qlik Script is that there is ALWAYS a way to make things work in an eloquent fashion. Today it was WildMatch, Pick and a simple +1 that saved me tons of nested IF statements so that I could assign a hierarchy to my data.
Hope someone down the road finds this tip useful.
