topic Re: Create new field by matching other fields in QlikView

Create new field by matching other fields

gerhardl — Wed, 11 May 2011 06:45:58 GMT

Hi,

Could someone please help me with the following:

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

Application extract - has application number only
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

gerhardl — Wed, 11 May 2011 07:20:34 GMT

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

Create new field by matching other fields

Wed, 11 May 2011 07:45:30 GMT

use left join() with where exists.

LEFT JOIN

LOAD [Account No],

[Transaction Type],

[Transaction Amount],

WHERE exists([Account No]);

Re: Create new field by matching other fields

gerhardl — Wed, 11 May 2011 08:07:18 GMT

get an error message - field not found.

Please have a look at my script (attached)

Re: Create new field by matching other fields

Wed, 11 May 2011 08:16:47 GMT

you didn't changed last table's [Account No] name. Change it and reload again.

Re: Create new field by matching other fields

gerhardl — Wed, 11 May 2011 08:26:43 GMT

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

Re: Create new field by matching other fields

gerhardl — Wed, 11 May 2011 08:47:07 GMT

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

Re: Create new field by matching other fields

Wed, 11 May 2011 09:33:37 GMT

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