Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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.
AssetID | AssetOwner | Outlook Version | Word Version | Visio Version | Count |
- | 9 | ||||
A1 | James | 2010 | - | - | 1 |
A1 | James | - | 2010 | - | 1 |
A1 | James | - | - | Standard2010 | 1 |
A2 | Fred | 2013 | - | - | 1 |
A2 | Fred | - | 2013 | - | 1 |
A2 | Fred | - | - | Professional2013 | 1 |
A3 | Neil | 2007 | - | - | 1 |
A3 | Neil | - | 2007 | - | 1 |
A3 | Neil | - | - | Standard2013 | 1 |
Outlook Version is a calculated dimension:
=if(SoftwareName='Outlook', SoftwareVersion)
Word Version is a calculated dimension:
=if(SoftwareName='Word', SoftwareVersion)
Visio Version is an expression:
=if(SoftwareName='Visio', SoftwareVersion)
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.
AssetID | AssetOwner | Outlook Version | Word Version | Visio Version | Count |
- | 3 | ||||
A1 | James | 2010 | 2010 | Standard2010 | 1 |
A2 | Fred | 2013 | 2013 | Professional2013 | 1 |
A3 | Neil | 2007 | 2007 | Standard2013 | 1 |
I suppose I am missing something simple but can't see it or find the answer on here.
Many thanks in advance.
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.
- Marcus
The count is just 1.
If I suppress Null on the calculated dimensions only the first one is returned.
AssetID | AssetOwner | Outlook Version | Word Version | Visio Version | Count |
- | 3 | ||||
A1 | James | 2010 | - | - | 1 |
A2 | Fred | 2013 | - | - | 1 |
A3 | Neil | 2007 | - | - | 1 |
The expression
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.
AssetID | AssetOwner | =if(match(SoftwareName, 'Outlook', 'Word', 'Visio'), SoftwareVersion) | Count |
A1 | James | 2010 | 1 |
A1 | James | Standard2010 | 1 |
A2 | Fred | 2013 | 1 |
A2 | Fred | Professional2013 | 1 |
A3 | Neil | 2007 | 1 |
A3 | Neil | Standard2013 | 1 |
I will attach a QVW with example data tables aswell.
See attachment.
- Marcus
Thank you Marcus, that worked great.
Set analysis is the key.
Made my day.
Thanks again, your a Star.