Hello,
I am trying to get a sense of how Qlik Sense loads table data through the data editor when you are trying to load 6 different tables. Essentially, the first 2 tables are the same other than the column "Division", the data is different between the 2. So I concatenated them so they would form one bigger table called Market Definition. See below...
[Market_Definition]:
LOAD
[DIVISION] AS Division,
[MARKET] AS Market,
LEFT([MARKET], 3) AS BC,
RIGHT([MARKET], 4) AS Sales_Locality,
[TA] AS Trade_Area,
REPLACE(REPLACE([DATAID], '.', ''), ' ', '0') AS Data_ID
FROM [lib://AttachedFiles/MKTDefn_201808.csv]
(txt, utf8, embedded labels, delimiter is ',');
CONCATENATE
LOAD
DIVISION AS Division,
MARKET AS Market,
LEFT(MARKET, 3) AS BC,
RIGHT(MARKET, 4) AS Sales_Locality,
TA AS Trade_Area,
REPLACE(REPLACE(DATAID, '.', ''), ' ', '0') AS Data_ID
FROM [lib://AttachedFiles/Nov 2018 MktDefn Chrysler.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',');
STORE Market_Definition INTO [lib://My QVDs (cag_t2421rm)/Market_Definition.qvd] (QVD);
DROP TABLE Market_Definition;The next 3 tables are the same other than the column "Year_Month", the data is different between the 3. I concatenated them as well and created a bigger table called Registrations. See below...
[Registrations]:
LOAD
@1 AS Year_Month,
@2 AS Data_ID,
@3 AS Segment,
@4 AS Co,
@5 AS Model,
@6 AS Series,
@7 AS Body_Style,
@8 AS Category,
@9 AS Vehicle_Type,
@10 AS Registration_Count
FROM [lib://DNA_Folder/IHS\_MARS_VIN\FCA_MARS_201612.txt]
(txt, codepage is 28591, no labels, delimiter is '|')
WHERE [@8] = 'R';
CONCATENATE
LOAD
PERIOD AS Year_Month,
TRACT AS Data_ID,
SEGMENT AS Segment,
CO AS Co,
MODEL AS Model,
SERIES AS Series,
BODY_STYLE AS Body_Style,
CATEGORY AS Category,
VEHICLE_TYPE AS Vehicle_Type,
NUMBER_OF_REGISTRATIONS AS Registration_Count
FROM [lib://DNA_Folder/IHS\_MARS_VIN\FCA_MARS_201712.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|')
WHERE [CATEGORY] = 'R';
CONCATENATE
LOAD
PERIOD AS Year_Month,
TRACT AS Data_ID,
SEGMENT AS Segment,
CO AS Co,
MODEL AS Model,
SERIES AS Series,
BODY_STYLE AS Body_Style,
CATEGORY AS Category,
VEHICLE_TYPE AS Vehicle_Type,
NUMBER_OF_REGISTRATIONS AS Registration_Count
FROM [lib://DNA_Folder/IHS\_MARS_VIN\FCA_MARS_201808.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|')
WHERE [CATEGORY] = 'R';
STORE Registrations INTO [lib://My QVDs (cag_t2421rm)/Registrations.qvd] (QVD);
DROP TABLE Registrations;The final table was left as is. See below...
VIO:
LOAD
REPLACE(REPLACE(DataId, '.', ''), ' ', '0') AS Data_ID,
AL_7_MY_VIO,
CC_7_MY_VIO,
CT_7_MY_VIO,
DC_7_MY_VIO,
DT_7_MY_VIO,
FI_7_MY_VIO AS FC_7_MY_VIO,
JT_7_MY_VIO,
RT_7_MY_VIO
FROM [lib://AttachedFiles/20181205_7MY_COIN_VIO_ByCline.xlsx]
(ooxml, embedded labels, table is Sheet1);
STORE VIO INTO [lib://My QVDs (cag_t2421rm)/VIO.qvd] (QVD);
DROP TABLE VIO;My end goal is to LEFT JOIN Registrations with Market Definition on Data_ID. I want all the Data_IDs from Market Definition and have any Data_IDs in Registrations join to those Market Definition Data_IDs. Just a simple LEFT JOIN.
Market Definition is the main and most important table in this query. No data can be taken away from it. This leads to table VIO. I need to do a LEFT JOIN with this table as well. It needs to join Market Definition. Essentially, VIO needs to LEFT JOIN to Market Definition. Below is the query I came up with after all the other code from above...
Union:
LOAD * FROM [lib://My QVDs (cag_t2421rm)/Market_Definition.qvd] (QVD);
LEFT JOIN
LOAD * FROM [lib://My QVDs (cag_t2421rm)/Registrations.qvd] (QVD);
LEFT JOIN
LOAD * FROM [lib://My QVDs (cag_t2421rm)/VIO.qvd] (QVD);
This works but I get a sense it didn't load properly. Maybe it has, but I am not a 100% keen on how Qlik Sense loads data. If anyone can explain to me how Qlik Sense would load this data, I would appreciate it. Remember, I am looking to keep Market_Definition which consists of 2 tables unioned together and have Registrations (3 tables unioned together) LEFT JOIN to Market_Definition by Data_ID. Then I also want to add VIO data so I can use it in the same dashboard. So, I would Market_Definition data again and have Registrations LEFT JOIN to Market_Defintion on Data_ID. So, it would seem like I have to giant tables at the end of all this processing in one dashboard. I'm assuming they would have to join together to become one even bigger table on Data_ID.
Also, is there a way to do all this without storing it into a QVD and in memory only? Thanks in advance!
