8 Replies Latest reply: Jul 6, 2011 8:53 AM by Gerhard Laubscher RSS

    New Field in script, calculated from other table

    Gerhard Laubscher

      Hi,

       

      I load data from three different extracts:

       

      1. Account Extract - daily snapshot of all accounts, e.g. customer details, customer's financial details, etc. This file gets replaced every day.
      2. Transaction Extract - daily extract of all transactions for the day. Gets concatenated every day.
      3. Application Extract - daily extract of all applications for the day, approved and declined. Gets concatenated every day.

       

      Using the transaction extract, I can easily in a table show the number of purchases a particular customer has made -

      Count ({$<[Tran Code]={35,37}>} [Tran Code])

       

      I would like to add this to the Account Extract. So in my script I would like to add a new field [Total Purchases], where it looks at the key field in the Transaction Extract (Account Number) and determines how many purchases a particluar account number has made.

       

      Load [Account No],

                                       // here I want to add [Total Purchases], showing the number of transactions from below extract where [Tran Code] is 35/37

              [etc,]

      From [......Account Extract.txt]

       

      Concatenate Load [Account No],

                                 [Tran Code],

                                 [Tran Amount],

                                 [etc.]

      From [.....Daily_Transaction_Extract_*.txt]

        • Re: New Field in script, calculated from other table

          Hello gerhard,

          if I got you right, this should point into the right direction:

           

          AccountExtract:

          Load *

          From [......Account Extract.txt];

           

          Left Join (AccountExtract)

               Load [Account No],

               Count([Tran Code]) AS [Total Purchases]

          from [. . . .Daily_Transaction_Extract]

          where [Tran Code] = 35 or [Tran Code] = 37

          Group by [Account No]

          ;

           

          HtH

          Roland

            • Re: New Field in script, calculated from other table
              Gerhard Laubscher

              Hi Roland,

               

              I'm a bit lost... I keep getting errors. Note that even though I only want to count Tran Codes 35 and 37 and I'm also loading Purchase Reversals. Here is part of my script:

               

              Account_Extract:

              Load *

              FROM [[L:\.........\Daily_AccountExtract_*.txt]
              (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines) ;

               

              left join (Account_Extract)
              LOAD Company,
                   [Legal Entity],
                   Product,
                   [Account No],
                     DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date],
                   num([Store No],0000) as [Store No],
                   ApplyMap('StoreCodeNameMap',[Store No],'Unknown') as [Tran Store Name],    
                   [Tran Code],
                   Count( [Tran Code]) as [Total Purchases],
                   If ((([Tran Code]=36 or [Tran Code]= 931) and [Trans Amount]>0), [Trans Amount]*-1, [Trans Amount]) as    [Trans Amount], 
                   [Trans Auth]
              FROM
              [L:\......\*.txt]
              (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)
              WHERE [Tran Code]=35 or [Tran Code]=36 or [Tran Code]=37 or [Tran Code]=931 and exists([Account No])
              Group By [Account No];

               

              I greatly appreciate the help.

               

              Gerhard

                • Re: New Field in script, calculated from other table

                  Hello Gerhard,

                  I think we are very close.

                   

                  Sorry, let me explain my idea post above more clearly:

                   

                  a) load all the three tables Account Extract, Transaction Extract, Application Extract from your files as usual.

                  b) then add a new field called [Total Purchases] to the table Account Extract using an left join -load. To do this kind of QV-Join correctly (it is like a merge, NOT a sql-join) I would use the KeyField (I assume this is [Account No]) between Account Extract and Transaction Extract. With this in mind let's code it similar to this (Sorry, didn't check syntax)

                   

                  Left Join (Account Extract)

                        Load [Account No],

                        count([Tran Code]) AS [Total Purchases]

                  Resident Transaction Extract

                  where [Tran Code] = 35 or [Tran Code] = 37 or [Tran Code] = 931

                  Group by [Account No]

                  ;

                   

                  Regards, Roland

                   

                  May be you want to use if() inside the count(), this is also possible