topic Re: left join not working with resident tables in QlikView

left join not working with resident tables

Anonymous — Mon, 05 Feb 2018 15:23:46 GMT

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

Re: left join not working with resident tables

sunny_talwar — Mon, 05 Feb 2018 16:43:57 GMT

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.

Re: left join not working with resident tables

Anonymous — Mon, 05 Feb 2018 16:45:37 GMT

Hi Mikis,

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

best regards

Re: left join not working with resident tables

Anonymous — Mon, 05 Feb 2018 17:02:17 GMT

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)?

Re: left join not working with resident tables

sunny_talwar — Mon, 05 Feb 2018 17:11:09 GMT

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

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

Re: left join not working with resident tables

rwunderlich — Tue, 06 Feb 2018 04:45:37 GMT

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

Re: left join not working with resident tables

Anonymous — Tue, 06 Feb 2018 09:08:32 GMT

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

Re: left join not working with resident tables

JordyWegman — Mon, 24 Sep 2018 12:29:13 GMT

Rob,

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

Re: left join not working with resident tables

rwunderlich — Mon, 24 Sep 2018 16:12:13 GMT

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 [

]

;

// F3 values will not JOIN

LEFT JOIN (Table1)

LOAD *, Null() as F2

Inline [

ID, F3

1, A

2, B

3, C

]

;

-Rob

Re: left join not working with resident tables

JordyWegman — Mon, 24 Sep 2018 19:58:17 GMT

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

]

;

Re: left join not working with resident tables

rwunderlich — Mon, 24 Sep 2018 20:08:22 GMT

In your example the empty fields are blank values, not null values.

-Rob