Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
jmcdermott
Contributor III
Contributor III

Qlikview scripting sum if statements

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

 

2 Solutions

Accepted Solutions
jwjackso
Specialist III
Specialist III

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

View solution in original post

jmcdermott
Contributor III
Contributor III
Author

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

View solution in original post

3 Replies
jwjackso
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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