IF Statement in script

[khaycock]

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?

[m_woolf]

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.

[jonathandienst]

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.

[khaycock]

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

[sunny_talwar · Accepted solution]

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;