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: 
Amberj_29
Contributor III
Contributor III

Composite Key Issues

Hi everyone,

I have been looking at a few old boards and questions about composite keys. I have manage to load the data, however, now that I have more than two tables my associations work for only half the data. For example, in the loaded data below "Country_Unified" (and the other key dimensions/metrics only now works for data from the "[Channel and Country]" workbook. When I look at the data model viewer it looks right and should work but it doesn't. When I had two tables - [Workbook1] and [Channels and countries] the associations between country and account number worked. It is when I added the third sheet [workbook2] and added another value for the key that is started to break. I did try to see if without the third key it would work, sadly it did not. Does anyone know how to assist on making a composite key across more than two tables? 

 

LOAD

    name,

    Notes,

    Tier,

    status,

    "account no." as "Account_Number1",

    Vlookup_Country as "Country_CMS",

    Vlookup_website,

    autonumber("account no."&'-'&Vlookup_Country) as Key

FROM [lib://Marketing and Sales - Internal:DataFiles/Workbook1.xlsx]

(ooxml, embedded labels, table is [Workbook1]);

 

LOAD

    CC_Isbn,

    CC_Title,

    "CC_Cust No." as "Account_Number2",

    CC_Portfolio as "Portfolio1",

    CC_Country as "Country_CC",

    "Country Other (search)",

    "CC_Reporting Month",

    latitude,

    longitude,

    CC_Region_Min,

    "CC_Region Higher tier",

    autonumber("CC_Cust No."&'-'&CC_Country&'-'&CC_Portfolio) as Key

FROM [lib://Marketing and Sales - Internal:DataFiles/Channels and Countries - for Qlik upload.xlsx]

(ooxml, embedded labels, table is [Channel and Country]);

 

LOAD

    EI__C,

    EI,

    "EI Product" as "Portfolio2",

    PRODUCT__C,

    Exams_Country as "Country_Exams",

    "Number of Exams",

    Month_of_Exam,

    autonumber(Exams_Country&'-'&"EI Product") as Key

FROM [lib://Marketing and Sales - Internal:DataFiles/Workbook2.xlsx]

(ooxml, embedded labels, table is [Workbook2]);

Common_temp:

load

"Account_Number1" as "C_Account Number",

"Country_CMS" as "Country_Unified"

resident  [Workbook1];

concatenate

Load

"Account_Number2" as "C_Account Number",

"Country_CC" as "Country_Unified",

"Portfolio1" as "Portfolio"

resident [Channel and Country];

concatenate

Load

"Country_Exams" as "Country_Unified",

"Portfolio2" as "Portfolio"

resident [Workbook2];

Common:

LOAD *,

autonumber("C_Account Number"&'-'&Country_Unified&'-'&Portfolio) as Key

Resident Common_temp;

drop table Common_temp;

 

I additionally want to add a fourth sheet and add another key - month as the fourth sheet will then merge by country and month to [Channel & Country] and [workbook2] and will join only by country to [workbook1]. I did manage to load it, but again I had the same issue that my keys only worked for half the data, so when I select "Country-Unified" I could not go across the Qlik App and look at all things occurring in a sad country. As I said I have been looking at old boards, if there is currently a better practice, please let me know. 

Any help is kindly appreciated. 

Many thanks,

Labels (4)
0 Replies