loop in data tables

[gerhardl]

Hi,

I have an Account_Table and it has an [Appl No].

I have and App_Table which also has an [Appl No], and a field called [Origin Code], with e.g. Origin 1 and Origin 2.

Not all the records from the Account_Table exist in the App_Table. These are migrated accounts (I can identify them this way in the Account_Table).

I want to give all the records that DO NOT exist in the App_Table an [Origin Code] of Origin 3.

For other reasons I have to load my Account_Table first, and then my App_Table.

What would be the best way to do this? I tried adding the [Origin Code] field to the Account_Table and saying if the account is migrated the [Origin Code] should be Origin 3, but then I create a loop.

Regards,

Gerhard

[datanibbler]

Hi Gerhard,

well, you have to load the Account_table first and then the App_table, and then you want to single out all the records - from the Account_table, I reckon - that do not exist in the App_table?

Right?`

Well, if you have both tables already loaded, that's easy: Just do a RESIDENT LOAD from the Account_table with a WHERE NOT EXISTS() clause referring to the ID in the App_table.

HTH

Best regards,

DataNibbler

[jonathandienst · Accepted solution]

Gerhard

I am a little confused. Where do you want to put the Origin 3 values if the record does not exist in the App Table? If you meant that you will create it in the Account Table, then make sure it is removed from the App Table.

This is what I would try:

MapOrigin:

Mapping LOAD [Appl No],

     [Origin Code]

FROM App_Table;

AccountTable:

LOAD [Appl No],

     ApplyMap('MapOrigin', [Appl No], 'Origin 3') As [Origin Code],

     ....

FROM Account_Table;

Now load App Table, but do not load Origin Code.

Does this do what you require?

Jonathan