Field not found error

[dominic_marchan]

Hi all,

I have a dataset which contains models with their inventory. Some of these items are composed of child items. So, when looking at inventory, the number you will see next to a parent item is 0. But the number you would want to see is the smallest inventory number of the child item.

I did this query in SQL as a proof of concept and then tried to replicate in Qlik.

Here is my attempt:

[Parent]:
LOAD
[Model],
[Inventory]
FROM [lib://AttachedFiles/InventoryData.xlsx]
(ooxml, embedded labels, table is Parent);

[Relationship]:
LOAD
[Model],
[Child]
FROM [lib://AttachedFiles/InventoryData.xlsx]
(ooxml, embedded labels, table is Relationship);

Left join(Parent)
Load *
Resident Relationship;

ParentChild:
Load
[Model] as ParentModel,
[Inventory] as ParentInventory,
[Child] as Model
Resident Parent;

*********error happens here as it says it cannot find Parent model********
Left Join(ParentChild)
Load
ParentModel,
ParentInventory,
min([Inventory]) as ChildInventory
Resident Parent
group By ParentModel, ParentInventory;

InventoryTwo:
Load
ParentModel as Model,
ParentInventory,
ChildInventory
Resident ParentChild;

Left Join(InventoryTwo)
Load
Model,
if(isnull(ChildInventory), ParentInventory, ChildInventory)
Resident Relationship
where isnull(Child);

drop table Parent;
drop table Relationship;
drop table ParentChild;

Thanks for the help

[Gysbert_Wassenaar · Accepted solution]

You create the field ParentModel in the table ParentChild. That field does not exist in the table Parent. So when you do a resident load from the table Parent it won't find that field in that table and that's why you get that error.

Try:

Left Join(ParentChild)
Load
Model as ParentModel,
Inventory as ParentInventory,
min([Inventory]) as ChildInventory
Resident Parent
group By Model, Inventory;