Maximum amount of left joins? Other solution?

[DanTrY]

Hello all,

I'm creating a qlik app with a less-than-perfect dataset which comes from a .csv file. 

This file has a setup in which I'm forced to make constant left-joins to an id number. 

For example, column A is 'line_id', column B is 'file_id', column C is 'question' and column D is 'answer'.

I have about 20 of the below left joins now as almost every 'answer' in D is information that should be linked to 'file_id' in B. 

Left Join (file1)

Load
[file_id],
answer as Apple_answers

Resident file1

Where question = 'Are apples green?'
;

Now, after about 20 left joins, the Load script gets stuck and keeps loading lines even though only a handful of lines should be found. (in fact, there's only 3781 lines in the CSV file).

Am I simply using too many left joins and is there a way better way to do this or is this a Qlik limitation I've run into? Or both?

[Or]

This seems like a super clunky way to pivot your data... and for some reason you're self-joining to the original table each time? That seems to make very little sense.

https://community.qlik.com/t5/New-to-Qlik-Analytics/transposing-pivoting-in-the-data-load-editor/td-...

Otherwise,

File:

Load Field1, Field2, Field3, Field4 From YourFile;

Temp:

Load distinct Field1, Field4 as QuestionName Resident Temp Where Something;

Concatenate Load distinct Field1, Field4 as QuestionName Resident Temp Where SomethingElse;

// Etc

Left Join(File)

Load * Resident Temp;

Drop Table Temp;

[rwunderlich]

I think you likely will want to use the Generic Load prefix to transpose the different questions into fields. 

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/

-Rob

[Kushal_Chawda]

@DanTrY  Also, instead of 20 joins, it is better to write 20 if conditions,

Load *
[file_id],
if(question = 'Are apples green?','Apple Answers',
if(question = 'Are Bananas yellow?','Banana Answers',
if(...))) as Answers_Flags
answer as Apple_answers
FROM file1;

You can then use Answers_Flags as Filter or in set analysis

[DanTrY]

Thanks for your suggestions, all. I hopefully have time next week to dive into this and will let you know.

[diegozecchini · Accepted solution]

Hi! I suggest a couple of alternative approaches to optimize your Qlik script and avoid excessive left joins:

1. Use Mapping Load Instead of Left Join
In Qlik, a Mapping Load can be used to create a mapping table that can then be applied using the ApplyMap function. This is often more efficient than multiple left joins.
For each 'question' type, create a mapping table and then use ApplyMap to populate your columns.
Example:

// Create a mapping table for Apple answers
Mapping Load
[file_id],
answer
Resident file1
Where question = 'Are apples green?';

// Apply the mapping to add the column in your main table
Load
[line_id],
[file_id],
[question],
[answer],
ApplyMap('AppleAnswerMap', [file_id], Null()) as Apple_answers
Resident file1;
This way, you reduce the number of joins and improve script performance.

2. Concatenate Instead of Joining
If the structure of your dataset allows it, consider using Concatenate instead of Left Join. This method merges datasets by stacking rows on top of each other rather than joining them side by side.
This approach works well when you have similar columns in multiple tables that you want to combine.
Example:

Concatenate (file1)
Load
[file_id],
answer as Apple_answers
Resident file1
Where question = 'Are apples green?';

[DanTrY]

While I'm sure it's not the most professional or good solution, moving all my Left joins into Mapping Loads did fix the problem and the load script is very fast.

I'm still a bit of a noob in Qlik so this was the most easy-to-understand and execute solution for me.