Problem with data model

[evgeniystuchalk]

Hello! I'm have some problem with data model. Here's my story.

First: I have two separate data bases as data sources (for two countries). Both DB contains same tables and fileds.

Second: I have 4 general tables in data model: leads, companies, tasks, notes. I'm joining tables from different DB in load script.

Leads joined to companies by field Company ID. All 4 tables connected to master calendar.

Third: for purpose of filtering Counties in my application, I have additional table amo_users, that contains names of responsibles. Each responsible have assotiated contry with him (marked with red), like:

LIB CONNECT TO 'rcs-kz';

LOAD name as Ответственный,

'Казахстан' as [Филиал];

SQL SELECT name

FROM `lsvidaniru_rcskz`.`amo_users` where `name`<>'Евгений Стучалкин';

join LIB CONNECT TO 'rcs-msc';

LOAD name as Ответственный,

'Москва' as [Филиал];

SQL SELECT name

FROM `lsvidaniru_rcsrf`.`amo_users` where `name`<>'Евгений Стучалкин';

When i'm using 'Responsible' field only in Leads table, everything is ok.

Fourth: tables Tasks and Notes have Responsible field too. And i'm need use them to display individual statistic of managers.

But when i'm loading this fields, my statistics become broken.

See details in that video.

[evgeniystuchalk · Accepted solution]

Solved by this method.

http://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

Synthetic fields was completely avoided.

[Anonymous]

If you have same tables and fields, then make the two connection for both the database and while your loading...

QV will do auto concatenation if both the tables have same fields

Else you can force with concatenate function...

Dont use join like you have done...

[its_anandrjs]

Hi,

You can try with some think this ways

LIB CONNECT TO 'rcs-kz';

rcs-kz:

LOAD name as Ответственный,

'Казахстан' as [Филиал];

SQL SELECT name

FROM `lsvidaniru_rcskz`.`amo_users` where `name`<>'Евгений Стучалкин';

Store rcs-kz into rcs-kz.qvd;

Drop table rcs-kz;

LIB CONNECT TO 'rcs-msc';

rcs-msc:

LOAD name as Ответственный,

'Москва' as [Филиал];

SQL SELECT name

FROM `lsvidaniru_rcsrf`.`amo_users` where `name`<>'Евгений Стучалкин';

Store rcs-msc into rcs-msc.qvd;

Drop table rcs-msc;

//Then do concatenate here as table structure is same

Load * from rcs-kz;

Concatenate

Load * from rcs-msc;

[evgeniystuchalk]

Not that answer, what i'm looked for, but thanks my code looks much better now.