topic Re: Join two resident tables in QlikView

Join two resident tables

Fri, 28 Dec 2012 14:51:13 GMT

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,

Re: Join two resident tables

chematos — Fri, 28 Dec 2012 17:32:05 GMT

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

Re: Join two resident tables

Fri, 28 Dec 2012 23:19:02 GMT

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.

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.

Re: Join two resident tables

Mon, 21 Jan 2013 11:51:23 GMT

Sorry for the late reply. It works.

Thanks Erica.