New Field in script, calculated from other table

[gerhardl]

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]

[#2]

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

[gerhardl]

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 [
(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

(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

[#4]

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

[gerhardl]

I feel like I am close now as well - but something is not right. I get the error message (picture attached) table not found.

I add Transaction_Extract: above my transaction extract, like I do for the accoutn extract. But then between the do I type what you suggest above but the Transaction Extract does not turn to Italics like it should....

Thanks for your patience with me.

Regards,

G

PS. I tried Transaction Extract, Transaction_Extract and (Transaction_Extract)

[#6]

Hi again,

take care of spelling exactly equal the field- and tablenames. It seems that you should put your "Transaction Extract" into square brackets (because of the blank) in the resident clause.

Even take care of case sensitivity in the whole word: Transaction_extract and Transaction_Extract --> These are two different tablenames.

Best is using table names (and also fieldnames) without any special chars like blanks. Or "german Umlaute" like I did when I was young. (In an international enviroment you can drive other people crazy with "üöä" in fieldnames :-))

RR

[gerhardl]

Hi Ronald,

I'm working on something else today, but as soon as I have time I will thoroughly go through my script and try and pinpoint my mistake - if I find it I will come back to you and let you know (and mark your answer as correct).

Thanks a million,

G

[#8]

Hello Gerhard,

thanks for your intermediate comment. So I am looking forward to your results . . .

RR

[gerhardl]

Hi Roland,

No Joy. No matter what I do, I cannot get the scipt to recognize the table name as a table name. I even tried changing the table name from TransactionExtract to ABC but it still doesn't work.

What am I doing wrong?

Regards,

G