left join won't work properly in script

[Andrey_Kosarev]

Hello everyone! 

I'm currently experiencing some strange behavior in an app that I have to maintain. 

There's a big main table that is formed at some point of the script: 

BigMainTable:

foreign_key	A bunch of other columns

and another table which is combined from two other tables with a left join

temp_table: 

load 
foreign_key,
doc_id,
doc_num
from [source1]
where condition = criteria

left join (temp_table)
load 
doc_id,
doc_date
from [source2]

So after that a have a temporary table that looks like this :

foreign_key	doc_id	doc_num	doc_date

At this point of the script all the field from the temp table are left-joined to the BigMainTable on the foreign key: 

left join (BigMainTable)
foreign_key,
doc_id,
doc_num,
doc_date
resident temp_table;

drop table temp_table;

The problem is that after the app if fully reloaded I can't see any values in the resulting fields, although the fields themselves are there.  As if the left join didn't find any coincidences in the  foreign key field. 

But if I store both tables into qvd files at the point of the script right before the join and try to join them in an outside app, it works alright, the values are there. Or if I just create an association between the tables by the foreign key field, it works as well. 

So why the app can't join the tables properly in the middle of the script, but has no problem with the same task in an outside test app? 

Thank you all in advance for your answers! 

[sunny_talwar]

Are you joining them only on foreign_key, in other words there are no fields in BigMainTable by the same name  as doc_id, doc_nu, and/or doc_date?

Also, are there invisible spaces in one or both foreign_key which might be causing this to happen? This can happen if the foreign_ket is sourced from different places.

[Andrey_Kosarev]

Hi, Talwar! 

The tables are joined on the FK field only. There are no more same-name fields. 

There are no spaces neither, otherwise it wouldn't work in an outside app.

[sunny_talwar]

@Andrey_Kosarev It would be difficult to know without more details, are you able to share the log file?

[Andrey_Kosarev]

Here's the part of the log where this happens

Sorry for the  russian column names 😃 

[marcus_sommer]

It sounds quite strange ... That it worked on the outside or (with certain adjustments within the app) with an association of the tables hints that key-values itself are ok. But there are scenarios thinkable which may impact the values and which may be different between your testings, for example: any NULL variables, variables for interpreting the data, the load-order of the field-values which also determines the data-interpretation ... and probably some more causes.

Beside this some of your tables might not be created at all or not with the tablename you expect them through the fact that all identically tables are auto-concatenated. Therefore do a check for it - some trace-statements which returns checks to the exists of certain tables/fields and/or the noofrows() and/or certain values per peek() or fieldvalue() could be very helpful to detect weird/unexpected things.

Another part which is easily overlooked and could cause issues is any kind of qualifying. Further if there are any ERRORMODE applied disable it to get proper error-messages.

Not directly related to your issue I suggest to consider to change your join approach to a mapping. No risks of loosing/doubling records, you could directly add a default-value for non-matching key-values and even nest this kind of logic and by larger datasets a mapping is usually much more performant as joins (multiple fields are not really a problem because you could string-concat them and cutting them again with subfield() within your target.

- Marcus

[Brett_Bleess]

Just adding a Design Blog post along one of Marcus' comments related to the Mapping comment:

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

If the advice Marcus posed did help you get what you needed, please be sure to close out the thread by using the Accept as Solution button on his post to give him credit for the help and let others know that did work for you.

Regards,
Brett