Left join

[#1]

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

[MK_QSL]

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;

[MK_QSL]

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

[#4]

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

Data:

LOAD A,

          B,

          C,

          [Project Number]

From Table1;

Left Join (Data)

LOAD D,

          E,

          F,

          [Project Number]

From Table1;

[#5]

I have tried this before It did not work

Thanks Though

[MK_QSL]

Can you load your sample script? or sample apps?

[#7]

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

AJ

[#8]

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

[#9]

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

[#10]

Use left Keep instead of left join