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

[tigra999]

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)

[petter]

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.

[tigra999]

Thanx for the answer Petter! I do think your solution is the right one but I get an error message, I am doing something wrong..

NEW is LOAD is working ok but when LOAD OLD I get the error message.

Illegal combination of prefixes

The error occurred here:

Inner Keep (Organizations) CONCATENATE LOAD sales_order_id, company_id, country_id,

NEW db:

LIB CONNECT TO 'NEW Database';

Inner Keep (Organizations) LOAD sales_order_id,

company_id,

    country_id;

FROM NEW.dbo.Organizations

OLD db:

LIB CONNECT TO 'OLD Database';

Inner Keep (Organizations) CONCATENATE LOAD sales_order_id,

company_id,

    country_id;

FROM OLD.dbo.Organizations

[tigra999]

Hi, correct script as per below (forgot to copy in the whole code), sorry.

NEW db:

LIB CONNECT TO 'NEW Database';

Inner Keep (Organizations) LOAD sales_order_id,

company_id,

    country_id;

[sales_order]:

Inner Keep (Organizations) SELECT "sales_order_id",

"company_id",

    "country_id"

FROM NEW.dbo.Organizations

OLD db:

LIB CONNECT TO 'OLD Database';

Inner Keep (Organizations) CONCATENATE LOAD sales_order_id,

company_id,

    country_id;

[sales_order]:

Inner Keep (Organizations) SELECT "sales_order_id",

"company_id",

    "country_id"

FROM OLD.dbo.Organizations

[petter · Accepted solution]

You can't use the CONCATENATE prefix in conjuction with an INNER KEEP. If you make sure that all the fields in the tables that need to be concatenated are exactly the same when it comes to the number of them and the names you will get an auto-concatenation and you won't need to use the CONCATENATE prefix at all.

[marinadorcassio1]

Why don't you just adapt what Petter told you ? Something like :

LIB CONNECT TO 'NEW Database';

Sales:

SQL SELECT

     sales_order_id,

     company_id,

     country_id

FROM NEW.dbo.Organizations;

LIB CONNECT TO 'OLD Database';

CONCATENATE (Sales)

SQL SELECT

     sales_order_id,

     company_id,

     country_id

   FROM OLD.dbo.Organizations;

[tigra999]

Thank you Petter! I really appreciate your help! Now it is working.