Simple resident table joins not working - Typo or am I missing something fundametal about how Qlik works?

[Scott2]

Hi All

I am trying to do a simple left join on resident table but am getting a field not found error message. I have tried qualifying with the table names but this does not work. 

The following error occurred:

Field 'ExpiryDate' not found

The error occurred here:

Left Join Load MaxExpiryDate Resident FruitDataGrouped where MaxExpiryDate = ExpiryDate

This is the test script below: 

FruitData:
NoConcatenate
Load * Inline [

FruitType,Quantity,DeliveryDate,ExpiryDate
Apple,34,,2023-08-30,2023-09-02
Apple,55,2023-08-30,2023-09-10
Apple,68,2023-08-30,2023-09-25
Apple,100,2023-08-02,2023-08-16
Orange,100,2023-08-02,2023-08-05
Orange,232,2023-08-02,2023-11-05
Orange,44,2023-08-02,2023-05-06
Orange,555,2023-08-02,2023-12-09
Mango,66,2023-10-15,2023-11-06
Mango,96,2023-10-15,2023-01-03
Mango,1116,2023-10-15,2023-04-01

];

NoConcatenate
FruitDataGrouped:
NoConcatenate
LOAD
FruitType,
MaxString(ExpiryDate) as MaxExpiryDate
Resident FruitData Group By FruitType;

NoConcatenate
FruitDataWithMaxExpiryDate:
NoConcatenate
Load * Resident FruitData;
Left Join
Load MaxExpiryDate Resident FruitDataGrouped
where MaxExpiryDate = ExpiryDate;

I also tried replacing the where clause with another field like FruitType but this also has field not found issue e.g. where [FruitData.FruitType] = [FruitDataGrouped.FruitType];

Any ideas why these where clauses are not working?

if I comment out the where clause i get the following success message, which leads me to a second question as to why in the "Data Manager "my table "FruitDataWithMaxExpiryDate" is not visible. It has default joined on FruitType as it created a synthetic key for FruitType and is visible in the "Data Model Viewer" but not in "Data Manager". Why is that? thanks!

[Or · Accepted solution]

Load MaxExpiryDate Resident FruitDataGrouped
where MaxExpiryDate = ExpiryDate;

There is no ExpiryDate field in FruitDataGrouped. You seem to be applying SQL syntax to a Qlik Load - you can't reference another table when you use a Qlik load, joins are automatically based on columns with identical names.

This entire thing looks like it could be simplified into a single load:

Load FruitType, FirstSortedValue(Quantity,ExpiryDate) as Quantity, FirstSortedValue(DeliveryDate,ExpiryDate) as DeliveryDate, Max(ExpiryDate) as ExpiryDate

From YourData

Group By FruitType;

or into two loads:

Load * From YourTable;

Right Join

Load FruitType, Max(ExpiryDate) as ExpiryDate

From YourTable

Group By FruitType;

[Scott2]

Thanks Or, I am new to Qlik and you're right I'd assumed it would be SQL like. Thanks for advising that "you can't reference another table when you use a Qlik load, joins are automatically based on columns with identical names." 

[Scott2]

Hi Or

I took your suggested script to test this but I'm clearly missing something else about how Qlik works as when I create a table to view the data the field "MaxExpiryDate3" is populated as expected but "MaxExpiryDate2" is not shown on all rows. There is a synthetic key FruitType added between resident tables FruitDataGrouped2 and FruitData so why isn't MaxExpiryDate2 populated on all rows? It seems it is joining on more than FruitType but that is the only field in the synthetic key. Perhaps when you add fields to a GUI table like this in addition to applying the join on the synthetic key it also checks all other fields in both the underlying tables for the rows  are the same? Thanks in advance for your help!

FruitData:
NoConcatenate
Load * Inline [

FruitType,Quantity,DeliveryDate,ExpiryDate
Apple,34,2023-08-30,2023-09-02
Apple,55,2023-08-30,2023-09-10
Apple,68,2023-08-30,2023-09-25
Apple,100,2023-08-02,2023-08-16
Orange,100,2023-08-02,2023-08-05
Orange,232,2023-08-02,2023-11-05
Orange,44,2023-08-02,2023-05-06
Orange,555,2023-08-02,2023-12-09
Mango,66,2023-10-15,2023-11-06
Mango,96,2023-10-15,2023-01-03
Mango,1116,2023-10-15,2023-04-01

];

FruitDataGrouped2:
NoConcatenate
Load FruitType, FirstSortedValue(Quantity,ExpiryDate) as Quantity, FirstSortedValue(DeliveryDate,ExpiryDate) as DeliveryDate, MaxString(ExpiryDate) as MaxExpiryDate2
Resident FruitData
Group By FruitType;

/*or into two loads:*/
FruitDataGrouped3:
NoConcatenate
Load * Resident FruitData;
Right Join
Load FruitType, MaxString(ExpiryDate) as MaxExpiryDate3
Resident FruitData
Group By FruitType;

[Or]

I'm not sure what you're doing there... it seems like you've applied both solutions when each is standalone, and in both cases, you've applied them over an existing table rather than directly?

[Scott2]

Hi Or, that's correct I have applied both over an existing data table to compare and try and understand how Qlik works. My real data has many tables with lots of related tables that need to be joined together without accidently creating cartersian joins or missing data as in this example where MaxExpiryDate2 is sometimes blank. Any idea why MaxExpiryDate2 is sometimes blank in the GUI Table?

I assume it's because FruitDataGrouped2 has 3 rows of data (as it's grouped by FruitType) where as FruitDataGrouped3 has all rows of data (as it's a right join on FruitData) and the GUI table is adding the field FruitDataGrouped2.MaxExpiryDate2 to the FruitData data only where all shared fields match? (hence 3 rows only)

[Or · Accepted solution]

By loading these overtop the existing table, you've created a messy data model. They are meant to be used separately so that you eventually wind up with only one table, and having them all in the model at the same time is causing things to behave in a manner that is not what one might expect.

I suggest you use them individually - that is, put them in the original load (or drop the original table after creating the second one) - and see if the behavior is what you expect. Trying to work with this triplicate schema isn't likely to be helpful.

Generally speaking, you need to make sure your data model works from a Qlik perspective, which typically means setting up tables that have the same field names for joins. In some cases this may not be the case, and you would have data separated in a way that isn't a classic model and then use front-end formulas (set analysis, usually) to "force" a join, but for the most part you want your data model to already be correctly joined if at all possible.

Whereas in SQL based reporting you would typically create individual queries for each output target, with Qlik your model needs to serve them all. This often means you'll want to use additional fields or flags to make it easier when creating things on the front end. In your original example, this would likely be handled using the ExpiryDate3 column that you already added, but you could also get this by using a front-end formula, typically something along the lines of:

Date(Max(total <FruitType> ExpiryDate))

[Scott2]

thanks, I deleted FruitDataGrouped3 to leave just FruitDataGrouped2 and then it showed the association was actually across multiple fields which explains why it wasn't populated on all rows in the GUI table.

Also thanks for the advise on the data model set up, much appreciated.