urgent: Joining two sql statements with more than 1 common filed in QV

[#1]

Hi, How to join two sql statements in qv without synthetic key as because I have 3 fileds in common and I have to join them using all three columns and I cannot rename fields

Ex: -

Query 1:

Select

col1,

col2,

col2,

----

----

from

tab1,

tab2,

tab3,

where condition..............

Query 2:

Select Col1, col2, col3, cq from tabx

SO I have to join Query1 & Query2 using Col1, col2, col3. How?

Thanks in Advance

[Miguel_Angel_Baeyens]

Hello,

You may use CONCATENATE keyword to just "union" your results:

LOAD *; SQL SELECT ...; CONCATENATE LOAD *; SQL SELECT ...;

Is this useful to you?

Regards

[#3]

Hi Miguel, Thanks for your reply...

No, I don't want to simple union the results.. i want to join using all three columns like

Query1.col1 = Query2.col1

OR/And

Query1.col2 = Query2.col2

OR/And

Query1.col3 = Query2.col3

[johnw]

You CAN remove the synthetic key, something like this:

KeyTable:
LOAD DISTINCT
col1 as col1x
,col2 as col2x
,col3 as col3x
,autonumber(col1 & ';' & col2 & ';' & col3) as key
RESIDENT Table1
;
OUTER JOIN (KeyTable)
LOAD DISTINCT
col1 as col1x
,col2 as col2x
,col3 as col3x
,autonumber(col1 & ';' & col2 & ';' & col3) as key
RESIDENT Table2
;
LEFT JOIN (Table1)
LOAD
col1x as col1
,col2x as col2
,col3x as col3
,key
RESIDENT KeyTable
;
LEFT JOIN (Table2)
LOAD
col1x as col1
,col2x as col2
,col3x as col3
,key
RESIDENT KeyTable
;
DROP FIELDS
col1
,col2
,col3
;
RENAME FIELDS
col1x as col1
,col2x as col2
,col3x as col3
;

Now, there may be a simpler sequence that would accomplish the same thing. However, although we've now eliminated our synthetic key, we haven't actually accomplished anything. This is basically exactly what a synthetic key DOES. It creates a separate key table with all of your key values, almost certainly gives each distinct combination a unique ID internally, and then puts this unique ID back on the original tables in place of the original fields. OK, I'm just guessing on the implemenation, but some brief testing I did showed very similar memory usage to a QlikView supplied synthetic key and an explicitly-supplied key like I created above.

The testing I did also showed very slightly better memory usage and performance in the synthetic key version.

So while you CAN eliminate the synthetic key by the method shown above, all it will do it waste processing time in the load. It doesn't really buy you anything. If you have an actual problem that you believe is CAUSED by the synthetic key, using the script above isn't likely to solve it, because it isn't likely a synthetic key problem. It's likely a more fundamental data problem that merely making an explicit key is unlikely to resolve.

In other words, unless you have an actual problem, don't worry about the synthetic key. If you do have a problem, you'll need to explain it, because merely removing the synthetic key is unlikely to fix it.

[#5]

Hi John

I'm totally new in QV. Thank you for the script. I copied your script and changed

RENAME FIELDS
col1x to col1
,col2x to col2
,col3x to col3
;

and its working brilliant.

Regards

Muncho

[#6]

Hello.

Is there any other solution like this?

Because its loading resident. I have much data inside table so its using much memory.

Syn keys working fine but i need to remove these.

[johnw]

munkhbayar wrote:Syn keys working fine but i need to remove these.

If they're working fine, why do you need to remove them? Do you think they're the cause of your memory problems? Very unlikely, though certainly you might have data model problems that are causing both the memory problems and the synthetic keys. The synthetic keys themselves are not likely the cause, though.

http://community.qlik.com/forums/t/31028.aspx