4 Replies Latest reply: Jan 7, 2016 9:07 AM by Peterjohn Griffiths RSS

    Combine multiple rows per asset in straight table.

    Peterjohn Griffiths

      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.