KEEP command - why to use that?

[datanibbler]

Hi,

I will admit right at the start of my post that I have not really read in depth the blog_posts about "Join and Keep" and such. I have had a look at them, though.

I think I understand pretty much how the one and the other work. What I do not understand is the WHY

=> Why would I want to avoid merging two tables and thus use a KEEP instead of a JOIN?

    (I can think of one, it makes the datamodel easier to associate with the tabs of the script and the charts on the GUI of my app - but
    that is more to do with internal programming standards and policy; Is there a technical reason, too?)

Thanks a lot!

Best regards,

DataNibbler

[Peter_Cammaert]

One good reason (amongst the many others) is that maybe you want to reduce a dimension table to the values that are actually used in your data model. Imagine that you have product information (all of it) loaded first. A LEFT KEEP with the facts left, and the products on the right will reduce product entries to the set that is referenced in the facts table. All without using exists(). Or another resident table. Simple.

[jagan]

Hi,

Let's check with this sample data

Orders:

Order#, Amount

1, 500

2, 300

OrderDetails:

OrderDetail#, Order#, Product

1, 1, XYZ

2, 1, ABC

3, 2, ABC

4, 2, QRT

5, 3, JKL

6, 4, MNO

Now, the order tables has Order# 1 and 2 but in details table you have 1,2,3,4.  In this scenario if you join the tables using Inner or Left join, the order 1 and 2 records are duplicated because we have multiple records in details table, when you sum up the Amount it won't match with the actual amount, so in this scenario Keep will be very very handy.

Orders:

LOAD

*

FROM Orders;

OrderDetails:

Inner Keep(Orders)

LOAD

*

FROM OrderDetails;

Now the join logic is applied between the tables, but the tables are not joined physically.

Hope this helps you.

Regards,

Jagan.

[datanibbler]

Hi jagan,

yes, I see. Thank you!

I have come across something similar in one of my apps - I made one field into an additional dimension to maximize the flexibility and user-friendliness of the chart and when testing I ended up with the figures doubled or tripled. I solved that by inserting a comment and then just dividing the value by 2 again ...

That was not a JOIN scenario, but I can see how that could happen. In that case, an INNER KEEP or LEFT KEEP would prove handy, though to be honest I can't really see its advantage over a simple WHERE EXISTS() clause ... (with the INNER or LEFT which are probably the ones most often needed - the RIGHT somehow runs contra to what human thinking would suggest the way it did since humans started heaping stones on top of each other ...)

Best regards,

DataNibbler

[Anonymous]

Hi,

I am trying to reduce loads by using INNER KEEP (<table>).

(Inner joins  which reduce number of rows loaded for subsequent tables).

Tried all kind of syntax combinations, but its not working.

Please note that I am loading from SQL, not qvd.

Thanks

[Peter_Cammaert]

Please start a new thread. You'll get better focus from participants. Thanks

[Anonymous]

Hi Peter,

I tried to, but I am getting a “You are not allowed to create or update this content”

J

[Peter_Cammaert]

Did you select the correct forum to start a new discussion? I guess "Connectivity & Data Sources -> Scripting" would be best.

[Anonymous]

Got it. Thanks