Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to load an IF statement in my script like this:
if(DocType='Invoice Receipt',AcctingQty,'') AS IR_Qty,
if(DocType='Acct. Adj.', AcctingQty,'') AS Adj_Qty
If the DocType is Invoice Receipt I want it to load the AcctingQty that are those DocTypes. Am I doing this correctly?
I'm stuggling to get this to load as both fields are in different tables, but even though the tables are joined, it still fails thinking that DocType isnt there even though it was declared at the beginning.
Any ideas?
Something like this
Fact:
LOAD DocType,
%AcctDocKey,
....
FROM....;
Left Join (Fact)
LOAD AcctYear,
AcctDoc & '-' & AcctDoc_Item1 as %AcctDocKey,
AcctDoc_Reference,
AcctingQty
Resident table;
DROP TABLE table;
Final_Fact:
LOAD *,
if(DocType='Invoice Receipt',AcctingQty,'') AS IR_Qty,
if(DocType='Acct. Adj.', AcctingQty,'') AS Adj_Qty
Resident Fact;
DROP Table Fact;
For this to work, AcctingQty needs to be in the same table as DocType.
Join the tables first. Then use a resident load to declare the new fields.
I dont see anything wrong with the statements you posted. I suggest that you post more of your script to assist with disgnosing your problem.
DocType was loaded into the fact table initially, AcctingQty is then being joined to the Fact table but by residenting another table. I would've thought I could've put the IFs in here or after here but I can't get it to work.
The join to the FACT looks like this:
left Join (Fact)
LOAD
AcctYear,
AcctDoc & '-' & AcctDoc_Item1 as %AcctDocKey,
AcctDoc_Reference,
AcctingQty
Resident table;
DROP TABLE table;
DocType is already in Fact as it was joined earlier so I'm not quite sure where you can put them
Something like this
Fact:
LOAD DocType,
%AcctDocKey,
....
FROM....;
Left Join (Fact)
LOAD AcctYear,
AcctDoc & '-' & AcctDoc_Item1 as %AcctDocKey,
AcctDoc_Reference,
AcctingQty
Resident table;
DROP TABLE table;
Final_Fact:
LOAD *,
if(DocType='Invoice Receipt',AcctingQty,'') AS IR_Qty,
if(DocType='Acct. Adj.', AcctingQty,'') AS Adj_Qty
Resident Fact;
DROP Table Fact;