topic Re: Understanding Left Join (I am still learning) in QlikView

Understanding Left Join (I am still learning)

Fri, 17 Jan 2014 07:10:52 GMT

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!

Re: Understanding Left Join (I am still learning)

Peter_Cammaert — Fri, 17 Jan 2014 07:34:23 GMT

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

Re: Understanding Left Join (I am still learning)

jagan — Fri, 17 Jan 2014 07:41:27 GMT

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.

Re: Understanding Left Join (I am still learning)

Fri, 17 Jan 2014 07:53:08 GMT

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

Re: Understanding Left Join (I am still learning)

NareshGuntur — Fri, 17 Jan 2014 08:44:45 GMT

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

Re: Understanding Left Join (I am still learning)

Peter_Cammaert — Fri, 17 Jan 2014 10:07:52 GMT

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

Re: Understanding Left Join (I am still learning)

nizamsha — Fri, 17 Jan 2014 10:37:21 GMT

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

Re: Understanding Left Join (I am still learning)

Mon, 20 Jan 2014 04:14:57 GMT

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?

Re: Understanding Left Join (I am still learning)

prasad_dumbre — Mon, 20 Jan 2014 04:36:43 GMT

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

Re: Understanding Left Join (I am still learning)

Mon, 20 Jan 2014 05:22:53 GMT

Hi,

Below link may help you

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