Joining tables - Field not found

[chris_johnson]

Hi,

I'm having a problem with joining tables that I'm hoping is something simple to solve. The script is as follows:

QVLedger:
//Information from Inspection Header
load
*,
applymap('RASResource_Map',[Work Description]) as [Resource No.];
load
*,
applymap('ResourceLink_Map',[Column Name]) as [Work Description];
load
"Job No.",
'Electrician Required' as [Column Name],
1 as Quantity
resident InspSheetHeader
where "Electrician Required" = 1;

//Add a few job fields
left join (QVLedger)
load
"Job No.",
InstructionDate as PricingDate,
[Job.External Work Code] as ExtWorkCode
resident Job;

//Add relevant Unit Price to the line
left join (QVLedger)
load
"Resource No.",
max([ResUnitPrice.UnitPriceKey]) as UnitPriceKey
resident ResUnitPrice
where ([ResUnitPrice.Effective Date] <= PricingDate)
group by "Resource No.";

On reload I recieve an error:

Field not found - <PricingDate>
left join (QVLedger)
load
"Resource No.",
max([ResUnitPrice.UnitPriceKey]) as UnitPriceKey
resident ResUnitPrice
where ([ResUnitPrice.Effective Date] <= PricingDate)
group by "Resource No."

Does anyone know why this might be happening and what I'm doing wrong? I'm creating the field PricingDate so I don't know why it is saying this doesn't exist.

Regards,

Chris

[matt_crowther]

Chris,

I may be missing something but from your posted script it looks as though the field 'PricingDate' is in the table 'QVLedger' and not 'ResUnitPrice' which is where your Where clause after the Resident load is trying to find it.

Hope that helps,

Matt - Visual Analytics Ltd

[chris_johnson]

Hi Matt,

That's right, PricingDate is in the QVLedger table. I'm trying to filter down the ResUnitPrice table to get the relevant line out of the ResUnitPrice table based on the Pricing Date.

The InstructionDate field which is renamed to be the PricingDate is the point at which we recieve a job. The pricing details are then fixed and the invoice is sent out at these rates.

Of course, we may implement new pricing which will have a new effective date. I need to pick up the correct pricing line based on the ResUnitPrice.Effective Date and the PricingDate.

Hope that makes sense.

Thanks for your reply,

Chris

[chris_johnson]

I filtered out the last Left Join statement and had a look at the fields created. In the QVLedger table the PricingDate field was actually called Job-1.PricingDate

I'm wondering if this has something to do with it.

[chris_johnson]

I have removed the bit where it qualifies the PricingDate field but it still can't find the field even though it creates it (I've checked this by commenting out the last left join and seeing what I got).

Is it a problem with trying to use a where clause when joining resident tables? It seems like I should be able to do this but everything I try is being rejected by QlikView.

I'm sure it wouldn't be this difficult in SQL.

Chris

[chris_johnson · Accepted solution]

Ended up having to load the table without the where clause that was giving me grief. Once this has done then I had to do another load in the script in order to take out the lines I didn't want. It's the same sort of solution as explained in the following post:

http://community.qlik.com/forums/t/36214.aspx

Thanks,

Chris