table joins and script errors

[nburton78]

I have the following script written, it works fine as is. 

BOMS:
LOAD*,
IXITM__Component_Item_Number_Short&Scheduled_Pick_Date__SDPDDJ-4 AS Component_DueDate;
SQL (...);
Left join(BOMS)
Load
[LIPQOH__Quantity_On_Hand],
[LIITM__Item_Number_Short]&[Snapshotdate] as Component_DueDate;
SQL (...);

I have tried to add this line before the first line of the script :

load *,
if ([LIPQOH__Quantity_On_Hand]<= COMPONENT_TOTAL, 'InventoryShort','InventoryAvailable')as InventoryStatus;

 but I get an error saying " Field [LIPQOH__Quantity_On_Hand] not found"

What am I doing wrong? How do I fix this?

[m_woolf]

Try adding that line below the BOMS: line.

[lironbaram · Accepted solution]

hi 

if i remember correctly the order preceding load while occur  before the left join part of the script 

so you'll need to do something like

BOMSTemp:
LOAD*,
IXITM__Component_Item_Number_Short&Scheduled_Pick_Date__SDPDDJ-4 AS Component_DueDate;
SQL (...);
Left join(BOMSTemp)
Load
[LIPQOH__Quantity_On_Hand],
[LIITM__Item_Number_Short]&[Snapshotdate] as Component_DueDate;
SQL (...);

BOMS:

load *,
if ([LIPQOH__Quantity_On_Hand]<= COMPONENT_TOTAL, 'InventoryShort','InventoryAvailable')as InventoryStatus

Resident  BOMSTemp;
drop table BOMSTemp;