Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello,
You may use CONCATENATE keyword to just "union" your results:
Is this useful to you?LOAD *; SQL SELECT ...; CONCATENATE LOAD *; SQL SELECT ...;
Regards
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
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.
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
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.
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.