Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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;
___________________________________________________________________________
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