Understanding Left Join (I am still learning)

[#1]

Hi everyone, I am trying to understand the concept of left joining

here is the code of what I did

Directory;

MainData:

LOAD Number,

     Exch,

     [Exch ID]

FROM

FullNumbers.xlsx

(ooxml, embedded labels, table is No);

SData:

LOAD Code as Number,

     Accnt_Num,

     CIDN,

     Product

FROM

[..\..\2013Database\s_20130814.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where exists (Number, Code);

left join (MainData)

load Number,

     Accnt_Num,

     CIDN,

     Product

resident SData;

MergedData:

load Number,

     Exchange,

     [Exchange ID],

     Accnt_Num,

     CIDN,

     Product

resident MainData;

Drop table MainData;

Drop table SData;

However, to my surprise, the result is nothing (blank)!

My idea was to load the first data set into "Main Data", load the 2nd data set into "SData", join them, load the joined data into "Merged Data" and then dropping the Main Data and SData for efficiency since I will only be manipulating the merged Data.

Am I not understanding the behaviour correctly?

On the other hand, if I comment out the drop table MainData, I'd get the merged data (but the table is not labeled "MergedData", rather "MainData")?

Thank you in advance!

[Peter_Cammaert]

There is nothing wrong with this approach, although it could be made shorter if you just drop SData and RENAME MainData TO MergedData.

Hint: put a STORE immediately after the LOAD of SData. If the resulting file contains no data, you'll know that something is wrong with the correspondence between Code and Number. Some formatting may be needed.

Peter

[jagan]

Hi,

Try this script

Directory;

MainData:

LOAD Number,

    Exch,

    [Exch ID]

FROM

FullNumbers.xlsx

(ooxml, embedded labels, table is No);

left join (MainData)

LOAD Code as Number,

    Accnt_Num,

    CIDN,

    Product

FROM

[..\..\2013Database\s_20130814.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where exists (Number, Code);

Regards,

Jagan.

[#4]

Can you verify the data for "Number" column in both SData and MainData have same values. I mean your left join is having ON condition as SData.Number = MainData.Number.

Thanks,

Angad

[NareshGuntur]

Hi Rinalldo,

The way you have written the script, by default the merged data table is getting concatenated with the main data. So, there will be no table called Mergeddata as all the data is added to the table Main data. And that is the reason you don't see any data if you drop the table Main data. Just use Noconcatenate before the table MergedData. Try the below script. Only one new addition(Noconcatenate)

Directory;

MainData:

LOAD Number,

     Exch,

     [Exch ID]

FROM

FullNumbers.xlsx

(ooxml, embedded labels, table is No);

SData:

LOAD Code as Number,

     Accnt_Num,

     CIDN,

     Product

FROM

[..\..\2013Database\s_20130814.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where exists (Number, Code);

left join (MainData)

load Number,

     Accnt_Num,

     CIDN,

     Product

resident SData;

Noconcatenate

MergedData:

load Number,

     Exchange,

     [Exchange ID],

     Accnt_Num,

     CIDN,

     Product

resident MainData;

Drop table MainData;

Drop table SData;

Cheers,

Naresh

[Peter_Cammaert]

Note: the problem with concatenated tables that Naresh is talking about below, disappears if you use the RENAME statement. Additional advantage: RENAME takes no time!

Peter

[nizamsha]

I will explain one by one

While loading the below table u should not have any problem

MainData:

LOAD Number,

     Exch,

     [Exch ID]

FROM

FullNumbers.xlsx

(ooxml, embedded labels, table is No);

This is the second table u r going to load again  .one common thing in these two table is [number]

so there must be two table say  MainData  and SData, these r two seprate table. one common filed [Number],qlikview will make it as association in between these 2 table.

but u r forcing to do left join to the MainData  ,now what qlikview is doing is ,its removing the association and doing left join combining two table in to one table.. That is main data

left join(MainData  )

SData:

LOAD Code as Number,

     Accnt_Num,

     CIDN,

     Product

FROM

[..\..\2013Database\s_20130814.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where exists (Number, Code);

This is what u r  trying to do i think ,for this u dont need to drop the table and dont want to take a resident and all ,

if u did int got  what u want then i will explain in next one

[#8]

Hi, thank you for your replies, everyone has been very helpful, although I adopted Naresh's solution since it seems to be the simplest by just adding one line.

Just to confirm if I am understanding correctly.

If I am loading from the same table (in this case, loading from MainData) and try to rename it to something else (in this case, MergeData), there won't be MergeData, but only MainData, is that correct?

I was assuming everytime I give something a name, it would be loaded into a different table? This looks like wrong assumption?

[prasad_dumbre]

If table structure contais same fields with same type of data, QlikView automatically concates them . . .

[#10]

Hi,

Below link may help you

http://community.qlik.com/groups/qvscriptor-from-schema-to-script/blog/2011/11/11/understanding-join...