topic Re: Left join in QlikView

Left join

Wed, 12 Feb 2014 19:24:27 GMT

Hi Everyone,

I am having trouble using left join, my laptop freezes whenever I load script containing left join

I am trying to load two tables which have a common field Project Number, I want to do left join because I want to see all the project numbers from table1 including the project numbers that exist in table1 but not table2 by using left join.

Apparently I am doing something wrong or it just the qlikview that is not supporting left join , my laptop completely freezes it took me 5 reboots to realize that the script is what causing it.

Could someone please help on an alternative solution like an If or where condition instead of left join?

Apprecite your help on this

Thanks,

Anusha

Re: Left join

MK_QSL — Wed, 12 Feb 2014 19:27:12 GMT

Left Join (Table1) Load * Resident Table2;

Drop Table2;

//Considering that ProjectNumber is the Field common between these two tables.

if Table 1 has ProjectNumber and Table2 has [Project Number] then

Left Join (Table1) Load *, [Project Number] as ProjectNumber Resident Table2;

Drop Table2;

Re: Left join

MK_QSL — Wed, 12 Feb 2014 19:29:08 GMT

You can also use..

Load * From Table1;

Load * From Table2 Where Exists (ProjectNumber);

2nd Table will load only those ProjectNumber which are in Table1..

Considering that ProjectNumber is the Field common between these two tables.

if Table 1 has ProjectNumber and Table2 has [Project Number] then

Load * From Table1;

Load * From Table2 Where Exists (ProjectNumber, [Project Number])

Re: Left join

Wed, 12 Feb 2014 19:33:14 GMT

Please make sure that the loading table must have atleast one common field names like below:

Data:

LOAD A,

[Project Number]

From Table1;

Left Join (Data)

LOAD D,

[Project Number]

From Table1;

Re: Left join

Wed, 12 Feb 2014 19:40:17 GMT

I have tried this before It did not work

Thanks Though

Re: Left join

MK_QSL — Wed, 12 Feb 2014 19:45:27 GMT

Can you load your sample script? or sample apps?

Re: Left join

Wed, 12 Feb 2014 19:48:45 GMT

Quick thought.

Usually during development on your local machine you don't do a full reload. You can run maybe 1000 lines and see how it behaves.

Then move it to Dev/QA server (where you will have more RAM) to process large amounts of data.

Thanks

Re: Left join

Wed, 12 Feb 2014 19:52:53 GMT

The data is not so large and rest everything works fine and quick but not left or right join

Re: Left join

Wed, 12 Feb 2014 19:54:22 GMT

Anusha,

try Keep left instead of left join.

Here is some info from QV webhelp

Keep

The keep prefix is similar to the Join prefix. Just as the join prefix, it compares the
loaded table with an existing named table or the last previously created data
table.

But instead of joining the loaded table with an existing table, it has the
effect of reducing one or both of the two tables before they are stored in
QlikView, based on the intersection of table data. The comparison made is
equivalent to a natural join made over all the common fields, i.e. the same way
as in a corresponding join. However, the two tables are not joined and will be
kept in QlikView as two separately named tables.

Example:

Inner Keep LOAD * FROM abc.csv;

Left Keep SQL SELECT * FROM table1;

tab1:

LOAD * FROM file1.csv;

tab2:

LOAD * FROM file2.csv;

.. .. ..

Left keep (tab1) LOAD * FROM file3.csv

Re: Left join

Wed, 12 Feb 2014 19:56:11 GMT

Use left Keep instead of left join

Re: Left join

Wed, 12 Feb 2014 20:05:18 GMT

Hi Anusha,

Did you try use Concatenate?

Data:

LOAD A,

[Project Number]

From Table1;

Concatenate

LOAD D,

[Project Number]

From Table2

Where ;

The result is all table 1 and table 2 but you can try build another tabla like this:

Select *

Resident Data

Where D = null();

That's no the solution but I believe It can help.

Please load an example or your script!

Regards,

Diego.

Re: Left join

Wed, 12 Feb 2014 20:27:29 GMT

Thanks for the inputs

Re: Left join

Wed, 12 Feb 2014 20:28:09 GMT

Left keep seems to be right, and it is not freezing the system

Thank You

Re: Left join

Wed, 12 Feb 2014 20:35:45 GMT

That's the best way to keep tables intact and simple.

Re: Left join

Wed, 12 Feb 2014 20:56:23 GMT

Now that the keep function works I am trying to seggregate the project numbers based on their existence,

I am only looking at the project numbers from table2 so I am using right keep and

right keep

LOAD

if([Project Definition]= exists([Project Number],[Project Definition]),[Project Definition],'Null') as [Project Number],

week,

cat,

so on fields

from table2.

but I am only seeing null label in my chart even the drill is gone.

Could you please help me on how can get all project numbers from table2 and see the drill for those which are present in table1 and those which are not present in table1 as null

Re: Left join

Wed, 12 Feb 2014 21:07:38 GMT

can u post sample qvw with excel data

Re: Left join

Wed, 12 Feb 2014 21:23:30 GMT

I am not allowed to share apparently!!!

Anyways could you help check conditions for two fields that would solve some of my problems

load count()

resident table1

where KAM='';

Project Definition belongs to table1 and KAM field belongs to table2

I know sql I can use table1.commonfiled=table2.commonfield

When I run this script obviously KAM is not in table1 and it isgiving me an error KAM field not found, but I need to check Proj DEf from table1 has null value or not when KAM is null , how do I link field from two tables?

Re: Left join

Wed, 12 Feb 2014 21:47:47 GMT

Instead of counting at Script level I counted in the expressions at chart level that worked

Thanks