Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
could someone please tell me what is wrong in this code? I am trying to categorize suppliers to A,B or C depending on the percentage of total sales as in: these suppliers make 80% of the spend and are therefore 'A', these suppliers make then to the 95% and are 'B' and the rest are 'C'... everything works until the IF statements where i get an error saying "if takes 2-3 parameters...
ABC_1:
LOAD
Supplier_id,
//[Invoiced amount incl. VAT],
[Invoiced amount incl. VAT] as ABC_spend
resident TEMPAP
order by [Invoiced amount incl. VAT] desc, Supplier_id;
ABC_2:
NoConcatenate
load *
,if(rowno()=1,ABC_spend, Peek(CumulSpend,-1)+ABC_spend) AS CumulSpend
Resident ABC_1;
total = Peek('CumulSpend',-1,'ABC_2');
DROP TABLE ABC_1;
ABC_CAT:
NoConcatenate
LOAD * ,
CumulSpend/$(total),
if(CumulSpend/$(total) <=.8, 'A',
if(CumulSpend/$(total) <=.95, 'B',
'C')) AS CLASS
Resident ABC_2;
DROP Table ABC_2;
try this
if((CumulSpend/$(total))<=.8, 'A',
if((CumulSpend/$(total))<=.95, 'B',
'C')) AS CLASS
Another alternative if you don't want to use variables. Not sure which method is better, but just to add another possibility:
ABC_1:
LOAD Supplier_id,
[Invoiced amount incl. VAT] as ABC_spend
Resident TEMPAP;
Left Join (ABC_1)
LOAD Sum([Invoiced amount incl. VAT]) as Total
Resident ABC_1;
ABC_CAT:
LOAD *,
CumulSpend/Total as CumulPercent,
If(CumulSpend/Total <= 0.80, 'A',
If(CumulSpend/Total <= 0.95, 'B', 'C')) as CLASS;
LAOD *,
If(RowNo() = 1, ABC_spend, RangeSum(Peek(CumulSpend), ABC_spend)) ax CumulSpend
Resident ABC_1
Order By [Invoiced amount incl. VAT] desc;
DROP Table ABC_1;