Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

pgriffiths
Contributor

Combine multiple rows per asset in straight table.

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.

   

AssetIDAssetOwnerOutlook VersionWord VersionVisio VersionCount
-9
A1James2010--1
A1James-2010-1
A1James--Standard20101
A2Fred2013--1
A2Fred-2013-1
A2Fred--Professional20131
A3Neil2007--1
A3Neil-2007-1
A3Neil--Standard20131

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.

   

AssetIDAssetOwnerOutlook VersionWord VersionVisio VersionCount
-3
A1James20102010Standard20101
A2Fred20132013Professional20131
A3Neil20072007Standard2013

1

I suppose I am missing something simple but can't see it or find the answer on here.

Many thanks in advance.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Combine multiple rows per asset in straight table.

See attachment.

- Marcus

4 Replies
MVP & Luminary
MVP & Luminary

Re: Combine multiple rows per asset in straight table.

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
Contributor

Re: Combine multiple rows per asset in straight table.

The count is just 1.

If I suppress Null on the calculated dimensions only the first one is returned.

   

AssetIDAssetOwnerOutlook VersionWord VersionVisio VersionCount
-3
A1James2010--1
A2Fred2013--1
A3Neil2007--

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.

   

AssetIDAssetOwner=if(match(SoftwareName, 'Outlook', 'Word', 'Visio'), SoftwareVersion) Count
A1James20101
A1JamesStandard20101
A2Fred20131
A2FredProfessional20131
A3Neil20071
A3NeilStandard20131

I will attach a QVW with example data tables aswell.

MVP & Luminary
MVP & Luminary

Re: Combine multiple rows per asset in straight table.

See attachment.

- Marcus

pgriffiths
Contributor

Re: Combine multiple rows per asset in straight table.

Thank you Marcus, that worked great.

Set analysis is the key.

Made my day.

Thanks again, your a Star.

Community Browser