Joining tables and creating a new field

[#1]

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

[#2]

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

[#3]

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;

[#4 · Accepted solution]

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

[#5]

Great, that's fixed it! Very many thanks Roland, really appreciate your help.

Best regards. Tony