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]
CAST(Date AS varchar)+'||'+Client_id AS [%KP_key]
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;
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.