Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Could someone please help me with the following:
I have the following two extracts I want to use in QV:
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:
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:
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
Please look at a cut-down version of my script:
LOAD [Appl No] as [Appl no],
FROM
(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes);
concatenate LOAD [Appl No] as [Appl no],
FROM
(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
use left join() with where exists.
LEFT JOIN
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]);
get an error message - field not found.
Please have a look at my script (attached)
you didn't changed last table's [Account No] name. Change it and reload again.
This is driving me crazy -
It loads fine now, but for some reason it gets 1024 Account Numbers - there should only be 1017.
Any idea why this is happening? I have NO experience with QV or anything resembling scripts, so please excuse my stupidity. If you can fix it, please send me the script in a text file (I have QV personal edition so won't be able to open a QV file).
Thanks
Is there any way I can add the person's account number to the Application Extract, by matching the [Appl No] of the application and account extracts.
Because I need to determine how many people mad a purchase in the same month as they were approved, and if I have no account number in my application extract I don't know how to do this.
Please look at the second sheet of my file - I REALLY need help with this, I either have to add an account number to the application extract, or I need to add an application number to the transaction extracts.
Also, I will have to add transaction and application extracts for the last year (one per month) and I'm not sure how to do this, I don't know when to concatenate and when to join
Ok i'm too lazy to read long text cuz my english is not so good ^^
I try follow your data structure.
first you loaded some tables and concatenate them.
second you loaded 2 tables and joined them existing account number.
And now you want join these 2 tables right?
let's say your concatenated tables table_1
and second table table_2.
after your script follow something like this:
left join (table_2)
load * resident table_1
where exists(appl_no);
drop table table_1;
hope it's helped and you understand my poor English
Muncho