Qlik Community

Qlik Sense Cloud Discussions

Highlighted
UM7
New Contributor

Qlik Sense Cloud Basic: JOIN not working to create a new View/Table from loaded tables

Hi All,

Started to explore three days back, I am facing issue with creating a view/table from the existing tables (Regions, Customers, Sales) by using Join as follows: 

The view/Table i am trying to create as follows:

[CustomerRegionView]:
Load [CustomerCode],
[CustomerName],
[RegionCode]
Resident Customers;

Join

Load [RegionCode],
[RegionName]
Resident Regions;

By this join, i want to create a new table called CustomerRegionView, So that it will have the fields of CustomerCode, CustomerName, RegionCode, RegionName. 

Also i want to generate another view by joining the three tables Regions, Customers and Sales with the total fields of Regioncode, RegionName, CustomerCode, CustomerName, TrID, Date, Sales. I tried this by the same way like above with join but this is also not happening. 

Master Tables List: 


[Regions]:
LOAD
[RegionCode],
[RegionName]
FROM [lib://AttachedFiles/Regions.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Customers]:
LOAD
[CustomerCode],
[CustomerName],
[RegionCode]
FROM [lib://AttachedFiles/Customers.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Sales]:
LOAD
[TrID],
[CustomerCode],
Date(Date#([Date], 'MM/DD/YYYY') ) AS [Date],
[Sales]
FROM [lib://AttachedFiles/Sales data.xlsx]
(ooxml, embedded labels, table is Sales);

However the tables are being created from the masters list but the view of the Join is not happening.  but it is creating a Autogenerated script on its own and creating a table Sales with 6 fields (RegionCode, RegionName, Trid, CustomerCode, Date, Sales) not sure how this is happening but my query is not created. 

Do we have any limitations in the Basic membership to use Queries like Joins etc? OR am i missing anything to create it?

Files attached for your reference. 

Please guide me on this..

3 Replies
Kalmer
Contributor

Re: Qlik Sense Cloud Basic: JOIN not working to create a new View/Table from loaded tables

I don't think you need the CustomerRegionView table since everything is in the Facts table anyway. But if there are  some architecture ideas behind it, i can understand why you would like to keep it.

So change the paths, etc and the script should work. I have commented the "CustomerRegionView" table out.

MAP_RegionNames:
Mapping load distinct
      RegionCode,
      RegionName
FROM [lib://My_Document_Path/Regions.xlsx]
(ooxml, embedded labels, table is Sheet1);


//THIS LINE IS NOT TO BE COMMENTED OUT. I USED QUALIFY TO HAVE DIFFERENT TABLE NAMES
/*
Qualify *;
Unqualify CustomerCode;
[CustomerRegionView]:
Load
    [CustomerCode],
    [CustomerName] as CustomerName,
    ApplyMap('MAP_RegionNames', RegionCode, 'Region Not Filled') as RegionName,
    if(len([RegionCode]) < 1, 'Region Not Filled', RegionCode) as RegionCode
FROM [lib://My_Document_Path/Customers.xlsx]
(ooxml, embedded labels, table is Sheet1);
Unqualify *;
*/

Sales:
LOAD
    TrID,
    CustomerCode,
    Date(Date#("Date", 'MM/DD/YYYY')) as Date,
    Sales
FROM [lib://My_Document_Path/Sales data.xlsx]
(ooxml, embedded labels, table is Sales);

left join(Sales)
LOAD
    CustomerCode,
    CustomerName,
    ApplyMap('MAP_RegionNames', RegionCode, 'Region Not Filled') as RegionName,
    if(len([RegionCode]) < 1, 'Region Not Filled', RegionCode) as RegionCode
FROM [lib://My_Document_Path/Customers.xlsx]
(ooxml, embedded labels, table is Sheet1);
UM7
New Contributor

Re: Qlik Sense Cloud Basic: JOIN not working to create a new View/Table from loaded tables

Thank you Kalmer for your reply. When i try to join these two tables, what happens is, That table is not being created seperately/Ignored by the Qlik Sense, Not sure why. Brief of the procedure i am following as follows:

1. Creating the three tables (Regions, Customers and Sales) from the Spreadsheets through the script by providing the paths of them. -It WORKs.

2. Trying to Create another table by using Join in the Script with path(As suggested by You) - but "map_id not found" error is coming, Will read about mappings and will resolve it. - However, when i tried to create with Join with paths also it is not creating this table. which means Qlik sense is ignoring this script.

3. autogenerated-section Script by Qlik Sense: it is creating a sales table by joining/identifying itself the RegionCode, RegionName etc NOT SURE HOW THIS IS HAPPENING.  Script has been given below, Seems like it is using the tables created in 1st Step and dropping them once the tables are created with new definitions, i dont understand how this is picking up the RegionCode and RegionName here without using any Join with Region table:

Unqualify *;

[Regions_temp_e29fa082-d52c-e9fc-bb18-1f12a85a]:
LOAD
	[RegionCode],
	[RegionName] AS [Regions.RegionName]
RESIDENT [Regions];
DROP TABLE [Regions];

[Sales_temp_d063c6e1-686f-bc5e-c099-c45b1989]:
LOAD
	[CustomerCode],
	[TrID],
	[Date],
	[Sales],
	[RegionCode] AS [Sales.RegionCode],
	[RegionName]
RESIDENT [Sales];
DROP TABLE [Sales];

RENAME TABLE [Regions_temp_e29fa082-d52c-e9fc-bb18-1f12a85a] to [Regions];

RENAME TABLE [Sales_temp_d063c6e1-686f-bc5e-c099-c45b1989] to [Sales];

It is very confusing to use this, Do we have proper guides, When i look through the YouTube videos or examples, those are all basic and does not give additional features which we can use or do complex things. 

Looking forward to hear from you and thanks again.

Kalmer
Contributor

Re: Qlik Sense Cloud Basic: JOIN not working to create a new View/Table from loaded tables

Hi!
Sorry for the late reply. Here we go:
1) Good to hear
2) Qlik is not ignoring script - it's giving you an indication with the error code of "Map id not found". So that means that it cannot find the mapping that we had in the code before. It is a bit strange since i ran the script with no errors myself. Just to let you know that Qlik is sensitive to letters (capital and non capital), so make sure that the mapping table name is the same on the Sales table:

MAP_RegionNames:
Mapping load distinct
      RegionCode,
      RegionName
FROM [lib://My_Document_Path/Regions.xlsx]
(ooxml, embedded labels, table is Sheet1);

//Other part of the script here

left join(Sales)
LOAD
    CustomerCode,
    CustomerName,
    ApplyMap('MAP_RegionNames', RegionCode, 'Region Not Filled') as RegionName,  //Make sure that the name is exactly as above
    if(len([RegionCode]) < 1, 'Region Not Filled', RegionCode) as RegionCode
FROM [lib://My_Document_Path/Customers.xlsx]
(ooxml, embedded labels, table is Sheet1);

3) Remove the autogenerated part - it's more for self service users who really don't know what they're doing. For drag and drop and no coding involved. If your "needs" are more difficult than a few tables i would not use self generated script at all.  So here is a tip how to remove it:
tip.png

Hope you can move on from here!
Good luck!