Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
evan_kurowski
Specialist
Specialist

LEFT JOIN syntax - why does the obvious not seem to be happening?

Greetings Qlik Community,

I am having an issue understanding why what seems to be the most basic of SQL functionality will not behave as anticipated in my application.  I am attempting to LEFT JOIN a two tables based on a mutually common key field.  The first step places a two field row in my table 'Averages'.  The second step I would like to tack on a third field to each row.  I can see the data when I concatenate both sets of data into the same table, however when I attempt to use the join syntax, something goes awry.

Concatenated.png


CONCATENATE ([Averages])
LOAD Symbol, Avg(Close) AS [60 Day Avg]
RESIDENT [TEMP_60] GROUP BY Symbol;


CONCATENATE ([Averages])
LOAD Symbol, Avg(Close) AS [30 Day Avg]
RESIDENT [TEMP_30] GROUP BY Symbol;

However, despite all my attempts, the tables continue to defy all attempts to combine them without losing data.  Here's the result I get when I LEFT JOIN the two tables, for some reason it keeps losing the data for certain columns.

Left_joined.png

CONCATENATE

([Averages])
LOAD Symbol, Avg(Close) AS [60 Day Avg]
RESIDENT [TEMP_60] GROUP BY Symbol;


// CONCATENATE ([Averages])
LEFT JOIN ([Averages])
LOAD Symbol, Avg(Close) AS [30 Day Avg]
RESIDENT [TEMP_30] GROUP BY Symbol;

There is something fundamental I am missing here and I'd like some assistance pinning it down.  I have tried various things to no avail.  Anyone have any ideas?  ~Sincerely, Befuddled with Bewilderment

3 Replies
Anonymous
Not applicable

Hello Qlik Community Members- this discussion has was posted previously and answered but accidentally deleted we are recreating the thread. If you have any helpful answers please feel free to respond.


Correct Answerby Daniel Rozental on Jun 29, 2011 1:20 PM

I think I know what the problem is

You're doing a For, it will work fine for the first symbol but left join will not work for the second symbol.

Joins in QlikView are done by all matching fields so the 2nd time you do the join it will do it by Symbol and AVG30 and will not be able to match any records.

Here's what you should do

[Averages]:
NOCONCATENATE LOAD Symbol FROM
[\\Users\Desktop\QlikView_materials\SECURITIES_HEADER.txt]
(
txt, codepage is 1252, embedded labels, delimiter is ',', msq);

FOR EACH SEC in $(vCodes) // Entire list stored in flat file securities.txt

TRACE LOADING 60 Day Avgs $(SEC);

[TEMP_60]:
FIRST 60
NOCONCATENATE LOAD Symbol, Close
RESIDENT [$(vCURRENTTABLENAME)]
WHERE Symbol = '$(SEC)'; //Filter by security

[TEMP_30]:
FIRST 30
NOCONCATENATE LOAD Symbol, Close
RESIDENT [TEMP_60];

Averages_Tmp:
LOAD Symbol, Avg(Close) AS [60 Day Avg]
RESIDENT [TEMP_60] GROUP BY Symbol;


LEFT JOIN(Averages_Tmp)
LOAD Symbol, Avg(Close) AS [30 Day Avg]
RESIDENT [TEMP_30] GROUP BY Symbol;


DROP TABLE [TEMP_60], [TEMP_30];

CONCATENATE ([Averages])
LOAD *
RESIDENT Averages_Tmp;


DROP TABLE Averages_Tmp;

NEXT

--------------------------------------

Helpful Answers by Miguel Angel Baeyens, krishnamoorthy

Miguel Angel Baeyens Jun 29, 2011 11:35 AM (in response to EvanKurowski)

Hello Evan,

In your script the first line is CONCATENATE, so the next LEFT JOIN will be added to the concatenated table, I mean, does the Averages: table has the Symbol field? Is it possible it has some blanks or spaces you can get rid of using Trim() ?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

----------------------------------------

Daniel Rozental Jun 29, 2011 11:37 AM (in response to EvanKurowski)

Makes no sense to me either, what happens if you build a new temp table and then do the left join on that?

TEMP_30_2:
LOAD Symbol, Avg(Close) AS [30 Day Avg]
RESIDENT [TEMP_30] GROUP BY Symbol;

LEFT JOIN ([Averages])

LOAD Symbol, [30 Day Avg]
RESIDENT [TEMP_30_2];

--------------------------------------------------

EvanKurowski Jun 29, 2011 12:11 PM (in response to EvanKurowski)

Greetings Miguel, Daniel.

Yes I have tried various methods of creating tables and checking key fields and to no avail.  Making independently named temp tables produced the same results.  Also I tried some basic keyfield validation attempts (forcing to text, trying to join on just the first letter, keeping only alphabetic characters, etc.. none worked)

Result when LEFT JOINED

Perhaps what would explain it is if all my LEFT JOIN functions or dll's or whatever magic is used to calculate this stuff were replaced by crazy pills?!?

CONCATENATE ([Averages])
LOAD Symbol, Only(Len(Trim(Symbol))) AS [Key 1 Len], Avg(Close) AS [60 Day Avg]
RESIDENT [TEMP_60] GROUP BY Symbol;


CONCATENATE ([Averages])
//LEFT JOIN ([Averages])
LOAD Symbol, Only(Len(Trim(Symbol))) AS [Key 2 Len], Avg(Close) AS [30 Day Avg]
RESIDENT [TEMP_30] GROUP BY Symbol;


---------------------------------

Daniel Rozental Jun 29, 2011 12:24 PM (in response to EvanKurowski)

What happens if you do a Join instead of a left join?


-------------------------------

krishnamoorthy Jun 29, 2011 12:26 PM (in response to EvanKurowski)

I tried the following script to reproduce/understand the error. But it worked correctly.

T1:

LOAD *Inline [

Symbol, C1

AAPL, 18

DUK, 20

];

T2:

LOAD *Inline [

Symbol, C2

AAPL, 118

DUK, 120

];

T3:

NoConcatenate LOAD * Resident T1;

Left Join (T3) LOAD * Resident T2;

DROP Table T1, T2;

---------------------------

Miguel Angel Baeyens Jun 29, 2011 12:32 PM (in response to EvanKurowski)

Sorry Evan,

I'm a bit confused. Why are you used the first CONCATENATE? I mean, is that on purpose? That may cause the error in the join.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

----------------------------------------------------

EvanKurowski Jun 29, 2011 12:40 PM (in response to Miguel Angel Baeyens)

Yes Miguel, the CONCATENATE statements are intentional.

The code within context is parameterized and iterating through a longer list of entries.  This functionality works by concatenating a pair of rows to my 'Averages' table on each pass.  However, when the second statement attempts to condense it to a single row per pass by substituting LEFT JOIN for CONCATENATE, I lose data entries.  ~E


------------------------------------

Daniel Rozental Jun 29, 2011 12:45 PM (in response to EvanKurowski)

Evan, as Miguel said, the problem probably lies on what you're doing before that first concatenate. Can you post your code?

Also, what happens if you do a "JOIN" instead of a "LEFT JOIN", can you try that?

-------------------------------------

EvanKurowski Jun 29, 2011 12:57 PM (in response to Daniel Rozental)

Here's the full section.  I tried using different joins, still the same result.

//LOAD blank table to initialize field structure

[Averages]:
NOCONCATENATE LOAD Symbol FROM
[\\Users\Desktop\QlikView_materials\SECURITIES_HEADER.txt]
(
txt, codepage is 1252, embedded labels, delimiter is ',', msq);

FOR EACH SEC in $(vCodes) // Entire list stored in flat file securities.txt

TRACE LOADING 60 Day Avgs $(SEC);

[TEMP_60]:
FIRST 60
NOCONCATENATE LOAD Symbol, Close
RESIDENT [$(vCURRENTTABLENAME)]
WHERE Symbol = '$(SEC)'; //Filter by security

[TEMP_30]:
FIRST 30
NOCONCATENATE LOAD Symbol, Close
RESIDENT [TEMP_60];

CONCATENATE ([Averages])
LOAD Symbol, Avg(Close) AS [60 Day Avg]
RESIDENT [TEMP_60] GROUP BY Symbol;


CONCATENATE ([Averages])
LOAD Symbol, Avg(Close) AS [30 Day Avg]
RESIDENT [TEMP_30] GROUP BY Symbol;


DROP TABLES [TEMP_60], [TEMP_30];


NEXT

[Averages]:

NOCONCATENATELOADSymbolFROM

(
txt,codepageis1252,embeddedlabels,delimiteris',',msq);

FOREACHSECin$(vCodes)// Entire list stored in flat file securities.txt

TRACELOADING 60 Day Avgs$(SEC);

[TEMP_60]:
FIRST60
NOCONCATENATELOADSymbol,Close
RESIDENT[$(vCURRENTTABLENAME)]
WHERESymbol= '$(SEC)';//Filter by security

[TEMP_30]:
FIRST30
NOCONCATENATELOADSymbol,Close
RESIDENT[TEMP_60];

CONCATENATE([Averages])
LOADSymbol,Avg(Close)AS[60 Day Avg]
RESIDENT[TEMP_60]GROUPBYSymbol;


CONCATENATE([Averages])
LOADSymbol,Avg(Close)AS[30 Day Avg]
RESIDENT[TEMP_30]GROUPBYSymbol;


DROPTABLES[TEMP_60], [TEMP_30];

NEXT

------------------------------------------------------

Daniel Rozental Jun 29, 2011 1:20 PM (in response to EvanKurowski)

I think I know what the problem is

You're doing a For, it will work fine for the first symbol but left join will not work for the second symbol.

Joins in QlikView are done by all matching fields so the 2nd time you do the join it will do it by Symbol and AVG30 and will not be able to match any recrods.

Here's what you should do

[Averages]:
NOCONCATENATE LOAD Symbol FROM
[\\Users\Desktop\QlikView_materials\SECURITIES_HEADER.txt]
(
txt, codepage is 1252, embedded labels, delimiter is ',', msq);

FOR EACH SEC in $(vCodes) // Entire list stored in flat file securities.txt

TRACE LOADING 60 Day Avgs $(SEC);

[TEMP_60]:
FIRST 60
NOCONCATENATE LOAD Symbol, Close
RESIDENT [$(vCURRENTTABLENAME)]
WHERE Symbol = '$(SEC)'; //Filter by security

[TEMP_30]:
FIRST 30
NOCONCATENATE LOAD Symbol, Close
RESIDENT [TEMP_60];

Averages_Tmp:
LOAD Symbol, Avg(Close) AS [60 Day Avg]
RESIDENT [TEMP_60] GROUP BY Symbol;


LEFT JOIN(Averages_Tmp)
LOAD Symbol, Avg(Close) AS [30 Day Avg]
RESIDENT [TEMP_30] GROUP BY Symbol;


DROP TABLE [TEMP_60], [TEMP_30];

CONCATENATE ([Averages])
LOAD *
RESIDENT Averages_Tmp;


DROP TABLE Averages_Tmp;

NEXT

------------------------

EvanKurowski Jun 29, 2011 3:03 PM (in response to Daniel Rozental)

I think you have it Daniel!  It's working with your syntax, thank you.  In a sense I think I was expecting this script to 'intuit' that I always only wanted to use Symbol as my sole keyfield, but I can see on the second iteration through, something went wrong.

I suppose my assumptions on left join functionality were incorrect as well, because I was thinking each successive pass would find the appropriate key-matched row and jam my calculated 30-day avg value into that column, even if the column was already present.   In a sense I was expecting UPDATE functionality, but I can see now on my second iteration I was trying to add an identially named column to a table in which it is already present.

So perhaps to clarify, I don't think that the issue was that the second iteration was not finding a match based on a dual-key field, but that it was attempting to join a column to a table when that column name already existed?  (The LEFT JOIN worked when I added the iteration value to the column name, esentially the table would've kept growing to the right).

Either way this is a big help in understanding Daniel.  I think I have fallen for this glitch on many, many applications before this. *sigh*.    dern you joins.

--------------------------------------------

FRANCISCO TAMINI Jun 29, 2011 4:35 PM (in response to EvanKurowski)

EvanKurowski escribió:

So perhaps to clarify, I don't think that the issue was that the second iteration was not finding a match based on a dual-key field, but that it was attempting to join a column to a table when that column name already existed?  (The LEFT JOIN worked when I added the iteration value to the column name, esentially the table would've kept growing to the right).

That's the way you look at it, you wanted QV to do the join by one column and QV is using 2, not the way QV resolves the join, it uses all matching field names and you don't get a value because the values don't match.


--------------------------------------

EvanKurowski Jun 29, 2011 5:53 PM (in response to FRANCISCO TAMINI)

I'm fairly certain I've isolated it to not the keyfield issues, the only keyfield it was attempting to use was 'Symbol'.  It was the 'silently failing' LEFT JOIN that was masquerading as a non-matched key.  Because the attaching column already existed, it just 'ignored' any other attempts to JOIN that particular field.

The following two examples illustrate what happened:

The first version will compile but fail on the second join (without error message), while the second version will allow it as long I attempt to keep the field names unique.  Ultimately I think what I was after was something along the lines of UPDATE but Daniel's version has got this moving again.

1:
LOAD *Inline [
Symbol
A,
B,
C,
D
];

T2:
NOCONCATENATE LOAD *Inline [
Symbol, C2
A, 118
D, 120
];

T3:
NOCONCATENATE LOAD *Inline [
Symbol, C2
B, 234
C, 123
];

Left Join (T1) LOAD * Resident T2;

Left Join (T1) LOAD * Resident T3;

DROP Tables T2, T3;

T1:
LOAD *Inline [
Symbol
A,
B,
C,
D
];

T2:
NOCONCATENATE LOAD *Inline [
Symbol, C2
A, 118
D, 120
];

T3:
NOCONCATENATE LOAD *Inline [
Symbol, C3
B, 234
C, 123
];

Left Join (T1) LOAD * Resident T2;
Left Join (T1) LOAD * Resident T3;

DROP Tables T2, T

Anonymous
Not applicable

Can you give the details about the Temp30 and Temp60 tables? Because if i create it and do, it seems working for me.

maxgro
MVP
MVP

post your script or check it with this small example

TEMP_60:

load * inline [

Symbol,Close

AAPL, 100

AAPL,150

AAPL,200

DUK,200

DUK,250

DUK,300

];

TEMP_30:

NoConcatenate

load * inline [

Symbol,Close

AAPL, 100

AAPL,150

DUK,200

DUK,250

];

//CONCATENATE

Averages:

NoConcatenate

LOAD Symbol, Avg(Close) AS [60 Day Avg]

RESIDENT TEMP_60 GROUP BY Symbol;

LEFT JOIN (Averages)

LOAD Symbol, Avg(Close) AS [30 Day Avg]

RESIDENT [TEMP_30] GROUP BY Symbol;

DROP Table TEMP_60, TEMP_30;

1.png