Qlikview scripting sum if statements

[jmcdermott]

Good afternoon all, 

I have been struggling though this for a week and I resigned that I can't get it.  In Microsoft access the working formula is 

Sum(IIf([Unit Code 3]="3102",-[201150],IIf(([201110]+[507200])=0,[121100]+[122100],IIf(([121100]+[122100]+[507200])<>0,-[201110],0)))) AS Amount 

in QV fieldname for Unit Code 3 is acct_unit3  .  The field for the account #'s acct is Match(acct,120100,121100,122100,122105,201110,201150,507200); 

and field for $ amount is dom_amount

A Coworker told me to do code like below

--------------------------------------------------------------

NoConcatenate

Test_01:
LOAD
           AffiliateCode|po_num|po_line, //key field
           sum(dom_amount) as Test_amt
Resident
           Temp_02
Where
               Match(acct,201100,507200)
Group By
AffiliateCode|po_num|po_line;

Left Join(Temp_02)

LOAD
AffiliateCode|po_num|po_line,
if(Test_amt = 0,1,0) as Condition_01
Resident
Test_01;

DROP Table Test_01;

------------------------------------------------------------------

and then to continue this for the rest of the conditions? He is swamped and I need more help.

Thanks,

Josh

[jwjackso · Accepted solution]

It looks like he is using the left joins to take vertically stacked fields and turning them to horizontal fields.

I don't want to send you down another rabbit hole, but would the script below achieve the same thing using preload statements (not tested, ymmv):

Temp:
load KeyField,
if (acct_unit3 = '3102',201150_Amt * -1,
if (201110_507200_Amt = 0,121100_122100_Amt,
if (121100_122100_507200_Amt != 0,201110_Amt * -1,0))) as Amount;

load KeyField,
acct_unit3,
sum(201150_Amt) as 201150_Amt,
sum(201110_507200_Amt) as 201110_507200_Amt,
sum(121100_122100_Amt) as 121100_122100_Amt,
sum(121100_122100_507200_Amt) as 121100_122100_507200_Amt,
sum(201110_Amt) as 201110_Amt
group by KeyField;

load
AffiliateCode|po_num|po_line as KeyField, //key field
acct_unit3
if (acct = 201150,dom_amount,0.0) as 201150_Amt,
if (Match(acct,201110,507200) > 0,dom_amount,0.0) as 201110_507200_Amt,
if (Match(acct,121100,122100) > 0,dom_amount,0.0) as 121100_122100_Amt,
if (Match(acct,121100,122100,507200) > 0,dom_amount,0.0) as 121100_122100_507200_Amt,
if (acct = 201110,dom_amount,0.0) as 201110_Amt
resident YOUR_TABLE where Match(acct,120100,121100,122100,122105,201110,201150,507200) > 0

[jmcdermott]

Good morning,

I put in the code that was suggested like below but I am getting an error.  What am I doing incorrectly?

Error:________________________________________________________________________ 

Invalid expression
load
AffiliateCode|po_num|po_line as KeyField,
acct_unit3,
if (acct = 201150,dom_amount,0.0) as aAmt,
if (Match(acct,201110,507200) > 0,dom_amount,0.0) as bAmt,
if (Match(acct,121100,122100) > 0,dom_amount,0.0) as cAmt,
if (Match(acct,121100,122100,507200) > 0,dom_amount,0.0) as dAmt,
if (acct = 201110,dom_amount,0.0) as eAmt
resident
Temp_02
where
Match(acct,120100,121100,122100,122105,201110,201150,507200) > 0

________________________________________________________________________________

Code I used below:

_______________________________________________________________________________

NoConcatenate
Temp_03:
load KeyField,
if (acct_unit3 = '3102',aAmt * -1,
if (bAmt = 0,cAmt,
if (dAmt = 0,eAmt * -1,0))) as Amount;

load KeyField,
acct_unit3,
sum(aAmt) as aAmt,
sum(bAmt) as bAmt,
sum(cAmt) as cAmt,
sum(dAmt) as dAmt,
sum(eAmt) as eAmt
group by KeyField;

load
AffiliateCode|po_num|po_line as KeyField, //key field
acct_unit3,
if (acct = 201150,dom_amount,0.0) as aAmt,
if (Match(acct,201110,507200) > 0,dom_amount,0.0) as bAmt,
if (Match(acct,121100,122100) > 0,dom_amount,0.0) as cAmt,
if (Match(acct,121100,122100,507200) > 0,dom_amount,0.0) as dAmt,
if (acct = 201110,dom_amount,0.0) as eAmt
resident
Temp_02 //YOUR_TABLE
where
Match(acct,120100,121100,122100,122105,201110,201150,507200) > 0;
___________________________________________________________________________

[jmcdermott · Accepted solution]

Code that works

NoConcatenate
Temp_03:
load AffiliateCode|po_num|po_line,
if (aAmt <> 0,aAmt * -1,
if (bAmt = 0,cAmt,
if (dAmt = 0,eAmt * -1,0))) as Amount;

load AffiliateCode|po_num|po_line,
acct_unit3,
sum(aAmt) as aAmt,
sum(bAmt) as bAmt,
sum(cAmt) as cAmt,
sum(dAmt) as dAmt,
sum(eAmt) as eAmt
Group By
AffiliateCode|po_num|po_line,
acct_unit3;

load
AffiliateCode|po_num|po_line, //key field
acct_unit3,
if (acct = 201150,dom_amount,0.0) as aAmt,
if (Match(acct,201110,507200) > 0,dom_amount,0.0) as bAmt,
if (Match(acct,121100,122100) > 0,dom_amount,0.0) as cAmt,
if (Match(acct,121100,122100,507200) > 0,dom_amount,0.0) as dAmt,
if (acct = 201110,dom_amount,0.0) as eAmt
resident
Temp_02
where
Match(acct,120100,121100,122100,122105,201110,201150,507200) > 0;
//YOUR_TABLE