Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I hope you can help me with the script for the following..............many thanks
I left Join 2 tables
TAB1
A 1
B 2
C 3
TAB2
A X
B Y
C Z
Resulting in
TAB1
A 1 X
B 2 Y
C 3 Z
I also want a new field that is a combination of both existing fields, so final result would be
A 1 X 1X
B 2 Y 2Y
C 3 Z 3Z
Hello Tony,
the joins in QV (they are not the same as in SQL) do work properly when you use a valid key (one or more fields) to link the tables together. If not, you may loose records or you may get a lot of sync-tables. Synctables are not bad, if you are familar with them otherwise they can be painful with funny results.
It looks like your key is (compno,specno,sitem). I think you are struggling with the "UNQUALIFY *;"-statement before the second join. It's a guess, but because of this the second join produces a huge sync-table, right?
Try this (key!)
Left Join (MIS_Details)
Load
compno,
specno,
sitem,
MIS_dets.ordered & repname AS repfield
Resident MIS_Details;
I for myself prefer createing (instead of using qualify at all) a surrogato key using sth like
autonumber(compno&specno&sitem) AS $MIS_ID
for all according tables. Don't forget to comment these three fields where not necessary.
HtH
Roland
Hello Tony,
assuming you joined your TAB1 with the fields F1, F2, F3 with F1 has 'A', 'B', 'C' as values. Then you can use an additional resident load like this:
Left Join(TAB1)
Load
*,
F2 & F3 AS F4
Resident TAB1;
with values of F4: 1X, 2Y, 3Z.
Regards, Roland
Hi Roland
Many thanks for your help.
I tried a testbed that worked fine but I cannot get it working on my qvw.
I think I can work around it by using a Load.. Resident, then drop original table; but I would like to know what I am doing wrong if you can help. Thanks again. Tony
Testbed based on your input as follows, works fine:
------
TAB1:
LOAD F1,
F2
FROM
C:\Users\tonyh\Desktop\TAB1.txt
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
TAB2:
LEFT JOIN(TAB1) LOAD F1,
F3
FROM
C:\Users\tonyh\Desktop\TAB2.txt
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Left Join(TAB1)
Load
*,
F2 & F3 AS F4
Resident TAB1;
--------
But I cannot see why it will not work with the following:
ODBC CONNECT TO sop (XUserId is ZWGXXRRMNbcKJ, XPassword is LdRYMYVMOTbSHaRL);
MIS_Details:
LOAD compno,
plantno,
wordnum,
specno,
sitem,
printno,
processno,
"type",
repno,
repname,
custslno,
processqty,
netsales,
boardarea,
boardweight,
addedvalue,
contribution,
lineno,
sopnum,
papstarval,
otherval,
labourval,
voverhead,
foverhead,
transcost,
periodno,
finyear,
"acc-no",
areano,
ldesc,
areadesc,
custdel,
flutes,
createdate,
despdate,
"del_note_ln",
whid,
delnoteno,
prodcd1,
adminCost,
packingCost,
handlingCost,
stereoCost,
formeCost,
unspecCost,
spareChar1,
spareChar2,
spareChar3,
spareDec1,
spareDec2,
spareDec3,
spareDec4,
spareDec5,
spareInt1,
spareInt2,
ratio1,
ratio2,
ratio3,
ratio4,
ratio5,
noPals,
qtyDel,
totCost,
manuQty,
"no-colours-bot",
"no-colours-top",
"use-of-box",
delDate,
badDataFlag,
badDataComment,
"grade-code",
grade,
"sht-name",
spareChar4,
spareChar5,
spareDec6,
spareDec7,
dateTimeLastCalc,
style,
compLevel,
spareChar6,
designWatse,
spareChar7,
spareChar8,
spareChar9,
spareChar10,
spareChar11,
spareChar12,
spareChar13;
SQL SELECT *
FROM PUB."MIS-Details";
QUALIFY *;
UNQUALIFY compno,
specno,
sitem;
MIS_dets:
Left Join LOAD compno,
specno,
sitem,
"int-length",
"int-width",
"int-depth",
ordered,
lastOrdered;
SQL SELECT *
FROM PUB."mis-specdet";
UNQUALIFY *;
Left Join (MIS_Details) Load
*,
MIS_dets.ordered & repname AS repfield
Resident MIS_Details;
Hello Tony,
the joins in QV (they are not the same as in SQL) do work properly when you use a valid key (one or more fields) to link the tables together. If not, you may loose records or you may get a lot of sync-tables. Synctables are not bad, if you are familar with them otherwise they can be painful with funny results.
It looks like your key is (compno,specno,sitem). I think you are struggling with the "UNQUALIFY *;"-statement before the second join. It's a guess, but because of this the second join produces a huge sync-table, right?
Try this (key!)
Left Join (MIS_Details)
Load
compno,
specno,
sitem,
MIS_dets.ordered & repname AS repfield
Resident MIS_Details;
I for myself prefer createing (instead of using qualify at all) a surrogato key using sth like
autonumber(compno&specno&sitem) AS $MIS_ID
for all according tables. Don't forget to comment these three fields where not necessary.
HtH
Roland
Great, that's fixed it! Very many thanks Roland, really appreciate your help.
Best regards. Tony