6 Replies Latest reply: Oct 16, 2018 3:59 AM by Peter Gustafsson RSS

    QS expression to show data from 2 databases into same pivot/graph

    Peter Gustafsson

      Hello, I am trying to make an expression to get data from 2 dbs into same chart/pivot but my expression fails every time.

       

      The databases are the identical (one is a archived db with old data) so each table are initially named the same but then I get synthetic key(s) problem when loading the data. Therefore I have re-named all of them in the Load script - ending with "_2" for data coming from the archive.

       

      Problem now is how to "merge"  this data into same pivot and graph... (i dont want to have separate charts etc)

       

      example 1:

       

      =if((nsc_state) <> 'X',sum({<component_code -= {'B_SPECIAL'}>} quantity),'0')

      =if((nsc_state_2) <> 'X',sum({<component_code_2 -= {'B_SPECIAL'}>} quantity_2),'0')


      example 2:


      =Year(order_date)

      =Year(order_date_2)

        • Re: QS expression to show data from 2 databases into same pivot/graph
          Petter Skjolden

          You should keep the data from the two databases in the same tables. Make sure to force each similar table to be concatenated by using the CONCATENATE prefix of the LOAD statement. Also add an extra field that identify which database the rows come from. This way you can use a set expression in a set analysis (or with an If or a selection) later to pick out the values from the database you want for comparative analysis.

           

          Sales:

             LOAD

                'A' AS db,

                *

             FROM;

             SQL SELECT * FROM dbnew..Sales;

           

          CONCATENATE LOAD

             'B' AS db,

             *

             FROM;

             SQL SELECT * FROM dbold..Sales;

           

           

           

          Then you can use the shared dimensions and fields to do your analysis with ease.

           

          For example:

               Sum( {<db={'A'}>} Sales) / Sum( {<db={'B'}>} Sales

           

          To get the ratio between Sales in the two databases.