Concatenate extra records to one table

[#1]

I've a 'SalesForce' table with 6 million records where records are repeated 3 times with unique SF_ID. For example, 2 million for SF_ID =101, 2 million for SF_ID = 102 and another 2 million for SF_ID = 103.

I've another table with 2.5 million records 'Master Table'. Now, I want to add (difference 2.5 - 2 milliion) 500,000 records from Master table to SalesForce table three times (500,00 with SF_ID = 101, 102 and 103 respectively). So, at the end result will be one SalesForce table with 7.5 million records and SF_ID not null.

I cannot hardcode SF_ID while concatenating as it keep changing every time. I need to variablize the SF_ID. Can someone help me with the whole syntax of variablizing and concatenating those extra rows? Thanks in advance!

[swuehl · Accepted solution]

Hi nims1983,

you would probably start with a table of your SF_IDs, since they are dynamic maybe using a distinct load (well, if the all IDs are located e.g. within the first 3 records, you could abort reading with a where clause):

SFIDTable:

LOAD Distinct SF_ID as SFIDNames

RESIDENT SalesForce where recno() <4;

Then do a join of this table with your MasterTable difference record or use a loop

  FOR i = 1 TO NoOfRows('SFIDTable')

SET vTempFieldName = FieldValue('SFIDNames', $(i));

RESULT:

LOAD

$(vTempFieldName) as SF_ID,

WHATEVERFIELDSTOCONCATENATE

from MasterTable;

NEXT

Hope this helps,

Stefan

[#3]

Thanks a lot Swuhel. it worked!