left/right join/keeep

[#1]

swuehl, John, Miguel, Rob, Kaushik, Erich, Karl, Michael, Oleg is there any diference between left keep n left join except their appearence as 2 tables and 1 table in tabular view(cntrl+t)

[avinashelite]

Refer this attachment

[avinashelite]

try this link:

Understanding Join, Keep and Concatenate

[datanibbler]

Hi Avinash,

I must admit I have never used this before, I rarely ever join tables - instead of optimizing the performance in this way, I prefer to keep it all understandable, enabling a non-expert user to associate

- a tab in my script

- a table in the data_model and

- one specific chart on the GUI

But it is certain to be helpful when you have tables from a database and potentially a lot of unnecessary data.

I am going to read this at home, it seems certainly worth knowing.

Put very simply, the KEEP does the same thing that you could be doing with a WHERE EXISTS() clause, only in both directions at the same time. Right?

Best regards,

DataNibbler

[avinashelite]

Hi DataNibbler

Thanks a lot for inputs, actually use of this function vary according to the scenario wise .

You can get the same results but maybe you can get a better performance with one of them according to scenarios.

First, if you have a small table A, and want to use it to filter another  table B.

Keep and Exits

If you load A and then use B with a left keep, you first loaded all the data in A.

If you had two million records in A and used B to get 100,000 you actually loaded all of them first.

If you first load B and then use Exists(), QV may check all the registers, but only the selected ones will be loaded.

If can reach the limits of memory in your system in the first case, but not in the second.

Also, you could use not exists() which may not be so easy to achieve this the left keep...

Join and Keep

Join will merge both the table because of which their may be junk data and memory consumption will be more.

Keep will not merge both the tables but it will reduce the data , when you dealing with snow flake model it may cause problem!!! 

so it all depends on the scenarios and requirement

Hope this answers a bit for your question.

Regards,

@vi