Combine multiple rows per asset in straight table.

[pgriffiths]

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.

[marcus_sommer]

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

[pgriffiths]

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.

[marcus_sommer · Accepted solution]

See attachment.

- Marcus

[pgriffiths]

Thank you Marcus, that worked great.

Set analysis is the key.

Made my day.

Thanks again, your a Star.