Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

gerhardl
Contributor II

Create new field by matching other fields

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

Tags (4)
7 Replies
gerhardl
Contributor II

Create new field by matching other fields

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

Not applicable

Create new field by matching other fields

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]);

gerhardl
Contributor II

Re: Create new field by matching other fields

get an error message - field not found.

Please have a look at my script (attached)

Not applicable

Re: Create new field by matching other fields

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

gerhardl
Contributor II

Re: Create new field by matching other fields

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

gerhardl
Contributor II

Re: Create new field by matching other fields

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

Not applicable

Re: Create new field by matching other fields

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

Community Browser