left join not working with resident tables

[Anonymous]

Hello everyone,

I have an issue in the data load editor, where I'm trying to left join Table2 on Table1.

my code says:

data_for_export:

noconcatenate LOAD

    field1,

    field2,

    value

resident Table1;

left join LOAD

field1,

totalSample

resident Table2;

So I would expect a table with field1, field2, value, totalSample

However, the totalSample is blank (it's added as a field, but it's blank).

note that there are in fact a lot more fields and many values are blank, but I don't think this matters?

I looked at the tables from every different angle, but I'm absolutely clueless

When I export both tables to txt, drop both and then do the EXACT same load from the csv's, everything goes well and the totalSample is added correctly.

This leads me to believe that it's not the method that is wrong, but something internal to qlik sense?

Does anyone have a clue? Is it because I have so many blanks? Is it because there's a synthetic keys between the tables?

Exporting resident tables, dropping them and uploading them again is slow but most of all sloppy. I would really like to know what causes the error.

Thanks in advance,

Mikis

[sunny_talwar]

The only reason I can think of is that no value of field1 from first table match with the field1 value from the second table. To check this, you can try to do a simple join (instead of Left Join) and see if there are totalSample which are not associated with any of the fields from first table. If this theory is true, you should be able to find what is causing the differences by selecting same field1's from both table and exporting them into Excel to see if they have extra spaces or if they are numbers, then may be there is some decimal mismatch.

[Anonymous]

Hi Mikis,

Could you pls show the script where you load both Table 1 and Table  2 for the first time?

best regards

[Anonymous]

Yeah I tried that before indeed. I exported Table1 and Table 2 before joining them and then tried an outer join.

The data itself is kind of confidential so I can't simply attach it, but I have a small sample of the result

as you can see, the 'total_factWeight' is always a single row, while it should be joined on the first 10 columns

Also, the fact that exporting it to csv, dropping the table and re-uploading it leads me to believe that it has something to do with that the table is in memory or something?

Or that qlik is unable to deal with joining on blanks (which I find hard to imagine)?

[sunny_talwar]

Yes, joining on blanks don't really work very nicely. If you are joining on multiple fields and some of them could be blank, I would suggest using a concatenated key to join

AutoNumber(Field1&'|'&Field2&'|'&Field3&'|'&....&'|'&FieldN) as JoinKey

or

Field1&'|'&Field2&'|'&Field3&'|'&....&'|'&FieldN as JoinKey

[rwunderlich]

QV will join on blanks, but not on nulls.  What QV version and SR are you using?

[Anonymous]

Hello Tulio,

I don't know how easy it is to understand this code for pro's, but it goes more or less like this

Table1:

NoConcatenate LOAD

    *

resident regionalData;

outer join Load * FROM [lib://dataLoad/regionWeights.xlsx] (ooxml, embedded labels, table is weight);

weight_mapping:

  mapping Load

  field1 & '|'& field 2 as lookupKey,

  weight as factWeight

  FROM [lib://QliksenseDataLoad/regionWeights.xlsx] (ooxml, embedded labels, table is weight);

Table2Temp:

NoConcatenate Load distinct

  field1,

  field2,

  field1 & ' | ' & field2 as lookupKey,

  ...some more fields,

  resident Table1;

Table2Temp_wWeight:

NoConcatenate load *,ApplyMap('weight_mapping',lookupKey,null()) as factWeight

resident Table2Temp;

drop table Table2Temp;

drop field lookupKey from Table2Temp_wWeight;

Table2:

field1,

...some more fields,

sum(factWeight) as total_factWeight

resident regionalData_factWeight

group By

field1,

...some more fields,

;

Note that Table2 takes the sum over field2

[JordyWegman]

Rob,

But when you have multiple key fields and one key field is empty (null), then QV will still join though, correct?

[rwunderlich]

Jordy,

To my knowledge there will be no join on that row if any field is null. Happy to see a counter example.

Table1:

LOAD *, Null() as F2

Inline [

ID

1

2

3

]

;

// F3 values will not JOIN

LEFT JOIN (Table1)

LOAD *, Null() as F2

Inline [

ID, F3

1, A

2, B

3, C

]

;

-Rob

[JordyWegman]

Rob,

Is leaving an empty row in an inline table the same as a null value?

Because in my example, the field FG is still joined even though key fields are missing, null(). But I didn't had complete columns with null values..

Metric:

Load * Inline [

%AB, %BC, %CD, %DE, EF

     , 1001, 10057, 43300,  store

900 ,      , 10057, 43300,  DC

]

;

Left Join (Metric)

Load * Inline [

%AB, %BC, %CD, %DE, EF, FG

     , 1001, 10057, 43300, store, NOS

900 ,      , 10057, 43300, DC, NOS

]

;