Guidance on Dashboard creation and linking Fields

[podge2019]

Hello,

Hopefully this is the correct form to post in as I'm relatively new to QlikView.

Currently have to create a dashboard and I am trying to understand how I would piece it together and load it.

Dimensions I have have are:
Excel with Servers, IP,OS, Owner, Owner Group, Owner Department
Excels with Keyword Logic related to OS, Servername and Vulnerability

Facts
Excel is a Qualys Output report with most of the above included in it so therefore I need to match it up.

I have the dashboard already created, however, I now need to link "Keyword Logic" to a owner / resolver.
(Guessing this should been done at the very start but again new to Qlik 🙂 )

On the Qualys report there is a "Title" field which is considered the "Vulnerability".

The Excel Keywould logic includes words such as "IBM" "Red Hat" "SSL" which are contained in the "Title" column.

If one of these words from the keyword logic is contained in the "Title" field in the Qualys report then it needs to link back to an "Owner" / Resolver from the Dimensions table Excel with Keyword logic.

There are a number of the fields across the 3 Excels that have the same Column name so would I have to create a link table for Servername?

Would I need to use a loader of some form or could a wildmatch work in the load script?
Also the logic I need to apply is a bit complicated to me anyway 🙂

The logic needs to be applied to across three different columns names in the keyword mapping i.e. as above - if "Title" includes certain keyword then it goes to one resolver group, if "OS" contains a word then it goes to a different resolver groups and then if "Server" name contains a certain set of characters it goes to another team.

Any guidance would be appreciated and I understand the above probably reads quiet complicated.

[Jonathan_Dienst · Accepted solution]

>>There are a number of the fields across the 3 Excels that have the same Column name so would I have to create a link table for Servername?

If the field is part of the logical associations, then the field name may not need to changed. If they have the same name but are not part of a logical association, then they need to be aliased to different names (or excluded if they are not really needed). Of course this also depends on the model structure.

>>Would I need to use a loader of some form or could a wildmatch work in the load script?

That's a design decision which would depend on a range of things. This does not look that complex, and if you are loading from Excel , you are already decoupled from the source system, so you don't absolutely  need separate loader. However if your design principles include splitting the data from the business rules, you may prefer to add a separate loader.

>>The logic needs to be applied to across three different columns names in the keyword mapping 

You cannot associate on a fragment from a field. You will need to split the fields into component parts to create the associations you need, or create link fields.  Keywords like Substring() and MapSubtring() may be of interest.

Rob Wunderlich wrote an interesting article on his website about fuzzy matching, which might be useful to you.

[rwunderlich · Accepted solution]

Hello,

Here's an example of linking your keywords to the Titles.

http://qlikviewcookbook.com/recipes/download-info/indexing-keywords-in-text/

-Rob

[podge2019]

@Jonathan_Dienst @rwunderlich  Many thanks for responding. Will take both responses onboard as I develop the app.