4 Replies Latest reply: Aug 19, 2011 5:15 AM by Tony Hollywood RSS

    Joining tables and creating a new field

      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

        • Joining tables and creating a new field

          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

            • Re: Joining tables and creating a new field

              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;

               

                • Joining tables and creating a new field

                  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