Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
RMMFCA
Contributor II
Contributor II

Structure And Load Tables In Data Editor

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!

Labels (1)
1 Reply
timpoismans
Specialist
Specialist

Hi there

Basically, Qlik runs the script from top to bottom (go figure, I know).

If it recognizes a second table has the same fields as a table loaded before, it will auto-concatenate the newly loaded table. That said, your script is perfectly doing as you tell it to do. You're expanding tables with data through concatenate and then finally left join them so you don't keep any data that's not present in your Market_Def. table.

 

The only way to check if it works as you thought it would, is to test it. Don't "get a feeling/sense" it doesn't work as you thought it would. Test it thoroughly, testing just a few won't do. If even just one piece of data isn't acting as it should, the fun begins.

 

But from what I can tell, as long as you don't have multiple fields shared between tables, the LEFT JOIN will only happen on the one common field between the tables.