7 Replies Latest reply: May 11, 2011 5:34 AM by Muncho Ts RSS

    Create new field by matching other fields

    Gerhard Laubscher

      Hi,

       

      Could someone please help me with the following:

       

      I have the following two extracts I want to use in QV:

      1. Application extract - has application number only
      2. Account Extract - has application number AND account number.

       

      I need to create an account number field in the Application Extract as well.

       

      So, if person A is [Application No] 123 in the application extract, I need it to match it to the [Application No] in the account extract. Then create a new field in the application extract called [Account No], and it must get this account number from the account extract. Does this makes sense?

       

      If not, read the rest please, but this might confuse even more:

       

      I have 3 extracts I am using:

      • Application Extract - this has an application number
      • Account Extract - this has the application number AND an account number
      • Transaction extract - this has only the account number

       

      I need to determine (from the transaction extract) how many of the new accounts made a purchase in each month. The problem is that the transaction and the application extract do not have common fields.

       

      So, what I need to do (I think) is load all 3 extracts, then add a new field to the application extract which CREATES the person's account number, so I can match it to the account numbers in the transaction extract.

       

      So, example for Person A:

      • In application extract - application number 123
      • In account extract - application number 123, account number 345
      • Transaction account - purchase by account number 345

       

      I need the application extract to have a new field with account number 345 so I can match my applications to my transactions.

       

      Thanks!

       

      Gerhard

        • Create new field by matching other fields
          Gerhard Laubscher

          Please look at a cut-down version of my script:

           

          LOAD      [Appl No] as [Appl no],

           

          FROM

          [C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Dunns & DFX\Turnover Exception Report\Application Extracts\Monthly_ApplicationExtract_A_01-MAR-2011_31-MAR-2011_01042011035843.txt]

          (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes);

           

          concatenate LOAD   [Appl No] as [Appl no],

           

          FROM

          [C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Dunns & DFX\Turnover Exception Report\Application Extracts\Monthly_ApplicationExtract_A_01-APR-2011_30-APR-2011_01052011112452.txt]

          (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes);

           

          LOAD [Account no] as [Account No],

                    [Appl no],

           

          FROM

          [Account Extracts\Daily_AccountExtract_A_30-APR-2011_01052011105615.txt]

          (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)

          where (exists([Appl no]));     *****UP UNTIL HERE IT WORKS FINE-- IT JUST LOADS THE 1070 new APPS

           

          LOAD      [Account No],

                         [Transaction Type],

                         [Transaction Amount],

           

          FROM
          [Transaction Extracts\Monthly_TransactionExtract_A_01-MAR-2011_31-MAR-2011_01042011040204.txt]
          (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)

          WHERE (exists([Account No]));       ***** HERE SOMETHING GOES WRONG IT NOW LOADS ALL 40 000 ACCOUNTS THAT MADE A PURCHASE - I JUST WANT PURCHASES BY THE 1070 NEW ACCOUNTS