topic Joining Resident Files in QlikView

Joining Resident Files

Wed, 10 Feb 2010 14:35:08 GMT

I am having trouble joining to resident tables. These are being loaded from QVDs as the data volumes are large.

I've searched the community and there is just too much information out there to filter down so I am going to have to post.

My two tables both have order, line, release as table specific columns and a concatenated key as the join.

No matter how i try to join it adds the rows or joins all to all.

Can anyone give me any tips?

In effect I have the following

Table A loaded with a.order, a.line, a.release, a.priorty.date, key.order.line.release (one record per after a couple of aggregation steps)

Table B loaded with b.order ,b.line ,b.release, b.ship.date ,key.order.line.release

Inner Join (Table B)
Load key.order.line.release
,a.priority.date as priority.date
Resident Table A

I'd like to do the aggregation in the final join to save processing but I have broken it out to take baby steps.

I'm also wondering if I can just load from joined QVDs instead of pulling in the resident tables.

Joining Resident Files

prieper — Wed, 10 Feb 2010 18:32:14 GMT

Can you post an example?

Joining Resident Files

Wed, 10 Feb 2010 18:55:53 GMT

Martin,

Try this:

Inner Join ([Table B])

although you can actually leave out the tablename anyway as it is the last logically created table.

In answer to your question re the QVDs, I believe it is generally more efficient to bring them into memory (no transformation so the load is optimised) and then fiddle with them as resident tables.

Hope this helps,

Gordon

Joining Resident Files

Thu, 11 Feb 2010 14:54:01 GMT

Thanks for the replies, I could not post a code example yesterday as I had it ripped apart working on it.

After going back to basics and reviewing the data and close review of the code I was able to get this to function.

The trick seemed to be the join fields, loading both tables with the join field having the same name rather than renaming it during the load from resident. That way it linked automatically in the inner join.

Re. :Re: Joining Resident Files

fseregaza — Thu, 11 Feb 2010 16:02:21 GMT

Hi Martin,

I'm not sure to understand your need. Do you want to join or to concatenate your tables ? In the latter, you can simply use the "Concatenate" function.

Ex:

TabA:

LOAD ... ;

Concatenate(TabA)

LOAD ... ;

This way, the second load will just add the new lines to the old ones. Is that not what you wanted to do ?

Best regards,

Franck SEREGAZA

Business & Decision

Re. :Re: Joining Resident Files

Thu, 11 Feb 2010 19:43:59 GMT

Definitely want the inner join as we are both using this to add a column and limit the data set,