topic IF Statement in script in QlikView

IF Statement in script

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: IF Statement in script

m_woolf — Fri, 12 May 2017 09:58:38 GMT

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.

Re: IF Statement in script

jonathandienst — Fri, 12 May 2017 10:00:13 GMT

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.

Re: IF Statement in script

khaycock — Fri, 12 May 2017 10:31:58 GMT

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

Re: IF Statement in script

sunny_talwar — Fri, 12 May 2017 11:06:19 GMT

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;