Problem with load script

[#1]

I have such code in script as is below (I've changed names and remove some dimensions). When I'm loading data as it is in the script - everything is fine. But this script it is not efficient on the large data (there are many syncs key so loading is very slow), so I wanted to add some keys or use "Concatenate". If I add keys then some values doesn't show on some dimensions:

my keys according to used dimensions in table:

CAST(Date AS varchar)+'|'+Product_id+'|'+Client_id AS [%KP_key]

or

CAST(Date AS varchar)+'||'+Client_id AS [%KP_key]

or

CAST(Date AS varchar)+'|'+Product_id+'|' AS [%KP_key]

and then I'm creating link table and I'm removing Client_id and Product_id fields.

When I add this and I want see what is Sum_c on Products, there are just zeros and after products there is a row with "-" id and whole sum. How it should look like -> Sum_c value should be repeated on every product.

Any ideas how can I do that or how to optimize my data model?

SumsC:
SELECT
  TIME Date,
  ID Client_id,
  Value Sum_c
FROM Sums
where ID in (select ID from Clients);

SumsP:
SELECT
  TIME Date,
  ID Product_id,
  Value Sum_p
FROM Sums
where ID in (select ID from Products);

Clients_data:
SELECT
  TIME AS Date,
  sth1 Sth1_id, //some dimensions
  sth2 Sth2_id,
  sth3 Sth3_id, //there are a few more - 8
  Value Client_cost,
  ID Client_id
FROM
Clients_data;


Products_data:
SELECT
  TIME AS Date,
  sth1 Sth1_id, //there are also 8 more dimensions as in Clients_data
  sth2 Sth2_id,
  sth3 Sth3_id,
  Value Product_cost,
  ID Product_id
FROM
Products_data;


Incomes:
SELECT
  TIME AS Date,
  ID Client_id,
  ID Product_id,
  Value Income,
  Amount Amount,
  Client_counter,
  Product_counter
FROM
Incomes;

======

Updates:
I have some progress. I found similar problem here: database - Handling multiple fact tables in Qlikview - Stack Overflow. So:
a) I did temp tables with whole data from database.

Temp_SumsC:
SELECT
  TIME Date,
  ID Client_id,
  Value Sum_c
FROM Sums
where ID in (select ID from Clients);

...

b) I loaded temp tables to right tables using key

SumsC:
Load Value,
     Date & '|' & Client_id AS %C_key
Resident Temp_SumsC

...

c) I added data from all tables to TempLinkTable

TempLinkTable:
LOAD Distinct
     Date, Client_id 
Resident Temp_SumsC

Concatenate(TempLinkTable)
...

d) I created LinkTable with all keys and dimension fields

LinkTable:
LOAD Distinct
     Date, Client_id , Product_id,
     Sth1_id, Sth2_id, Sth3_id,
     Date & '|' & Client_id %C_key, 
     Date & '|' & Product_id %P_key,      
     Date & '|' & Client_id & '|' & Sth1_id & '|' & Sth2_id & '|' & Sth3_id %CA_key, 
     Date & '|' & Product_id & '|' & Sth1_id & '|' & Sth2_id & '|' & Sth3_id %PA_key, 
     Date & '|' & Client_id & '|' & Product_id %CP_key
Resident TempLinkTable

e) I removed all unnecessary tables

DROP Table Temp_SumsC;
...

After script reloaded, the Sum_c and Sum_p started work, but Client_cost and Product_cost still doesn't work.

[#2 · Accepted solution]

I solved this problem in the following way: I've changed subsection c). Instead of this:

TempLinkTable: 
LOAD Distinct 
    Date, Client_id 
Resident Temp_SumsC 

Concatenate(TempLinkTable) 
...

I used this:

TempLinkTableP:
LOAD Distinct      Date, Client_id, Product_id
Resident           Incomes
outer join (TempLinkTableP)
LOAD Distinct      Date, Product_id, Sth1_id, Sth2_id, Sth3_id
Resident           Products_data;

NoConcatenate

TempLinkTableC:
LOAD Distinct      Date, Client_id, Product_id
Resident           Incomes
outer join (TempLinkTableC)
LOAD Distinct      Date, Client_id, Sth1_id, Sth2_id, Sth3_id
Resident           Clients_data;

NoConcatenate

TempLinkTable:
LOAD Distinct      Date, Product_id, Client_id, Sth1_id, Sth2_id, Sth3_id
Resident TempLinkTableP;
Concatenate
LOAD Distinct      Date, Product_id, Client_id, Sth1_id, Sth2_id, Sth3_id
Resident TempLinkTableC;

Drop Table TempLinkTableC;
Drop Table TempLinkTableP;

And it works - the values are correct. Unfortunately, instead of fast loading data, the app still works slow, but this problem is for another question.

Thanks for all help Marcus.

[marcus_sommer]

Slow data-loads could be often improved with incremental load methods and using of optimized loadings - within this post Advanced topics for creating a qlik datamodel there are several links to those topics.

- Marcus