Discussion Board for collaboration on QlikView Scripting.
I have an issue I have not been able to solve.
I have a dashboard that loads a list of assets in one table and a list of software on another like so.
UNQUALIFY *; Assets: LOAD * Inline [AssetID, AssetOwner A1,James A2,Fred A3,Neil]; UNQUALIFY *; Software: LOAD * Inline [AssetID, SoftwareName, SoftwareVersion A1,Outlook,2010 A2,Outlook,2013 A3,Outlook,2007 A1,Word,2010 A2,Word,2013 A3,Word,2007 A1,Visio,Standard2010 A2,Visio,Professional2013 A3,Visio,Standard2013];
As you can see, these tables are joined by AssetID.
I am trying to create a straight table that combines the results of both of these tables with only one line per asset.
I have tried using calculated dimensions and expressions but both give the same result, one line per software with output like the following.
Outlook Version is a calculated dimension:
Word Version is a calculated dimension:
Visio Version is an expression:
Non of these work how I want them to work.
I want to have output is the following format but cant' get Qlikview to do it in a straight table.
I suppose I am missing something simple but can't see it or find the answer on here.
Many thanks in advance.
Go to Solution.
Have you suppress NULL enabled (tab presentation)? How looked the expression for count?
Further possible could be to use a pivot-table and a dimension like:
=if(match(SoftwareName, 'Outlook', 'Word', 'Visio'), SoftwareVersion)
and hide NULL on these dimension.
The count is just 1.
If I suppress Null on the calculated dimensions only the first one is returned.
if(match(SoftwareName, 'Outlook', 'Word', 'Visio'), SoftwareVersion)
still outputs multiple rows but instead of creating different columns as I wanted in the example, it puts them all under one column as its all done under one expression and has no way to show what software each version is for.
I will attach a QVW with example data tables aswell.
Thank you Marcus, that worked great.
Set analysis is the key.
Made my day.
Thanks again, your a Star.