topic Re: Using Load Resident on 2+ Tables in QlikView

Using Load Resident on 2+ Tables

Wed, 30 Jul 2014 21:06:29 GMT

I am trying to pull fields out of two different tables to perform a calculation that will be a new field. I am trying to do this with two load resident statements but cannot get the syntax to work. I am hoping someone can correct my code or provide info for a better way to perform the same calculation. From the table viewer everything is correctly joined, could there be an easier way to perform calculations to make new fields in script than load statements? Thank you.

[Tap/Charge Tons]:

Load

[Charge Tons],

Resident [TotalChargeMaterial];

Load

[Tap Weight],

([Plant] & [Melt Number]) as %MeltNumberKey,

Resident [Melt Log];

[Tap Weight]/2000/[Charge Tons] as [Tap/Charge Tons] Group By %MeltNumberKey

//Not sure if Group By is needed or not to Join tables correctly

Re: Using Load Resident on 2+ Tables

marcus_malinow — Wed, 30 Jul 2014 21:11:32 GMT

Hi,

you're going to need at least one key between your two tables. Do you have Plant and Melt Number available in your TotalChargeMaterial table?

Marcus

Re: Using Load Resident on 2+ Tables

rustyfishbones — Wed, 30 Jul 2014 21:15:28 GMT

Create the Calculations in a Preceding Load

Re: Using Load Resident on 2+ Tables

Wed, 30 Jul 2014 21:18:25 GMT

Yes, both tables have the %NektNumberKey

Re: Using Load Resident on 2+ Tables

marcus_malinow — Wed, 30 Jul 2014 21:27:33 GMT

Ok then, maybe something like this

_temp:

Load

([Plant] & [Melt Number]) as %MeltNumberKey

[Charge Tons]

Resident [TotalChargeMaterial];

LEFT JOIN (_temp)

Load

[Tap Weight],

([Plant] & [Melt Number]) as %MeltNumberKey,

Resident [Melt Log];

FINAL:
NOCONCATENATE LOAD

%MeltNumberKey,

Sum([Charge Tons]) as [Charge Tons],

Sum([Tap Weight]) as [Tap Weight],

Sum( ([Tap Weight] / 2000) / [Charge Tons] ) as [Tap/Charge Tons]

RESIDENT _temp

Group By %MeltNumberKey;

DROP TABLE _temp;