Join two resident tables

[#1]

Hi all,

Below is the code I had written in the load script.

1.KPI works perfectly fine as Ward, Net_cost are both the fields in the file  DATA therefore I used Resident function.

3.KPI works perfectly fine as Site_data , Supcode_data , Ward , Net_cost all exist in the file DATA therefore I used Resident function.

The problem is with 2.KPI as Ward is in the file DATA and Valuegross is in the file called STOCK. I am assuming that i should combine the resident function that includes DATA and STOCK. Unable to figure out the expression and dont know if there is a different way of approach.

LOAD 1 As KPI,
     MonthStart(ControlDate) As ControlDate,
     Sum(If(Ward = 'EX',Net_cost)) As Numerator,
     1 As Denominator

Resident [data]
GROUP BY MonthStart(ControlDate);

Concatenate
LOAD 2 As KPI,

     MonthStart(ControlDate) As ControlDate,
     Sum(If(Ward = 'EX',Net_cost)) As Numerator,
  Sum(Valuegross)as Denominator

GROUP BY MonthStart(ControlDate);

Resident [data]

Concatenate
LOAD 3 As KPI,
     MonthStart(ControlDate) As ControlDate,
     Sum(If(Site_data = '431' and Supcode_data = 'NSPS' and Ward = 'EX',Net_cost)) as Numerator,
     Sum(If(Site_data = '431' and Ward = 'EX', Net_cost)) as Denominator

Resident [data]
GROUP BY MonthStart(ControlDate);

Regards,

KV

[chematos]

Resident looks up in a table you load previously and only in that table, not in files.

[#3 · Accepted solution]

You can join resident tables just like any other tables with the syntax left / right join ()... from ....;

In your case one option would be to left join just the valuegross field in  the Stock table to the data table, just before the KPI2 load.

EG

left join (data)

Load

ControlDate,

Valuegross

resident stock;

load 2 as kpi....

....

resident data

If you needed to keep the table then you could drop the field from it after the KPI 2 to avoid comlicated keys.

drop field valuegross from data;

Do let me know if this works.

[#4]

Sorry for the late reply. It works.

Thanks Erica.