Calculated field from two unrelated tables

[Anonymous]

Hi community.

I need some guidance please. I'm a newby and trying to allocate costs to jobs, and I want to do this in the script and without joining the tables as the dataset is very large.  There is a common field "Field A" in both tables and another field "Field G", which could match Field C or Field D in the first table. See image for example please. In addition to that I need to allocate all the ass_code = 0 fields where TableA.FieldA = TableB.FieldA ( FieldG does not exist in FieldC or FieldD)

I basically want to do something like this somehow:

TableC:

Load *

Field E * $(vField H) AS Job_cost

Resident Table A

Where Field A.TableA = Field A.TableB

and Field C.TableA xor Field D.TableA = Field G.TableB

OR perhaps like below (but can't have 2 identical fieldnames & this doesn't solve the ass_code = 0 issue)

TableA:

FieldA & FieldC as Link_Key

FieldA & FieldD as Link_Key

TableB:

FieldA & FieldD as Link_Key

Your assistance will be much appreciated !

[Anil_Babu_Samineni]

Would you provide Excel ?

[vinieme12]

Hi Chantelle,

Your output doesn't match the description you posted.

For example your output for Job_no 1 shows, Jan records matched with Jan and Feb from table 2, also where Ass_code should either be 1 or T10; but you have matched even with 0's and 2's.

[vinieme12]

<<<<<Modified>>>>>

Try like below, first we join records having Truck_ID in ass_code and then records having  Trailer_ID in ass_code

Jobs:

LOAD JobNo,

MonthYear,

Cus,

Truck_id,

Trailer_id,

Portion%,

MonthYear&'_'&Truck_id as Key_TruckID,

MonthYear&'_'&Trailer_id as Key_TrailerID

From TABLEASource;

left join(Jobs)

Cost:

LOAD Recno(),

Expense,

Balance,

MonthYear&'_'&ass_code as Key_TruckID

From Cost_Source

WHERE wildmatch(ass_code,'T*')=0 and ass_code > 0;

left join(Jobs)

Cost2:

LOAD Recno(),

Expense,

Balance,

MonthYear&'_'&ass_code as Key_TrailerID

From Cost_Source

WHERE wildmatch(ass_code,'T*')>0;

left join(Jobs)

Cost:

LOAD Recno(),

Expense,

Balance,

MonthYear

From Cost_Source

WHERE  ass_code = 0;

[Anonymous]

Hi Vineeth

Thank you for your response.

There are a few rules here:

For each record in table A it should "loop" table B and find all the records with the same MonthYear

Then:

Rule 1) If   ass_code = 0 , then calculate portion x balance (regardless of Field C and D) AS Overhead

Rule 2) If ass_code = either 1 or T10, then alculate portion x balance AS Direct_Cost

Else : False = 0

So I believe my output is correct. But should have been in 2 columns "Overhead" and "Direct_Cost"

For the 2's the test is False so result = 0

[vinieme12]

need to allocate all the ass_code = 0 fields where TableA.FieldA = TableB.FieldA

So to Allocate ass_code = 0  atleast the MonthYear should match correct?

[vinieme12]

Can you post an excel sample? it'll be easier to understand that way

For each record in table A it should "loop" table B and find all the records with the same MonthYear

1) Key could be combination of MonthYEar & Truck_id or MonthYear & Trailer_id?

2) Records with Ass_code  = 0  should be matched with all records for the same YearMonth?

[Anonymous]

Correct