if(sum() ) in load script

[#1]

Hi,

i have in script:

unqualify '*';
TB1Hierarchy2:
LOAD [Hierarchy-1 ID],
[Hierarchy-2 ID],
[Hierarchy-3 ID],
[BP-Account Group] as BPAccount,
If([BP-Account Group]='Corporate',1,
If([BP-Account Group]='Key',2,
If([BP-Account Group]='Standard',3,
If([BP-Account Group]='Private',4,
If([BP-Account Group]='Nursery',5))))) as AccGrID,
[SoldTos No.],
[Orders Created No.]
FROM C:\Hierarchy.xls (biff, embedded labels, table is Tabelle1$);
Left Join (TB1Hierarchy2)
Load distinct
[Hierarchy-1 ID],
Count([Hierarchy-1 ID]) AS NumberHierarchy,
sum([SoldTos No.]) As TotalSumSoldTo,
sum([Orders Created No.]) as TotalSumOrderCr,
if(sum([Orders Created No.])<6, 'A'
if(sum([Orders Created No.])<12, 'B'
if(sum([Orders Created No.])<21, 'C'))) as AOF,
min(AccGrID) as AccGrpID
Resident TB1Hierarchy2
Group by [Hierarchy-1 ID] Order By [Hierarchy-1 ID];

what is on if(sum(..) not correct

if(sum([Orders Created No.])<6, 'A'
if(sum([Orders Created No.])<12, 'B'
if(sum([Orders Created No.])<21, 'C'))) as AOF

Thanks

[Anonymous]

I had a similar need. And I solved it with temporary table with sums. After that you can create another table using temporary table as resident depending on TotalSumOrderCr field value

Rgds,
Artjoms

[#3]

Hi,

Thanks,

what i doing wroung:

i have add the temporary tabel(TB1Hierarchy3) but the script not going. become error

unqualify '*';
TB1Hierarchy2:
LOAD [Hierarchy-1 ID],
[BP-Account Group] as BPAccount,
If([BP-Account Group]='Corporate',1,
If([BP-Account Group]='Key',2,
If([BP-Account Group]='Standard',3,
If([BP-Account Group]='Private',4,
If([BP-Account Group]='Nursery',5))))) as AccGrID,
[SoldTos No.],
[Orders Created No.]
FROM C:\Hierarchy.xls (biff, embedded labels, table is Tabelle1$);
Left Join (TB1Hierarchy2)
Load distinct
[Hierarchy-1 ID],
Count([Hierarchy-1 ID]) AS NumberHierarchy,
sum([SoldTos No.]) As TotalSumSoldTo,
sum([Orders Created No.]) as TotalSumOrderCr,
min(AccGrID) as AccGrpID
Resident TB1Hierarchy2
Group by [Hierarchy-1 ID] Order By [Hierarchy-1 ID];
TB1Hierarchy3:
Left Join (TB1Hierarchy2)
Load
[Hierarchy-1 ID],
NumberHierarchy,
TotalSumSoldTo,
AccGrpID,
if(sum(TotalSumOrderCr)<6, 'A'
if(sum(TotalSumOrderCr)<12, 'B'
if(sum(TotalSumOrderCr)<21, 'C'))) as AOF
Resident TB1Hierarchy3
Group by [Hierarchy-1 ID] Order By [Hierarchy-1 ID];

[Anonymous]

You are using If in grouped Load again. My idea was to avoid If(Sum(...)) expression.

In your updated script you aren't using a temporary table TB1Hierarchy3, cause Left Join is used.

My idea was:

TB1Hierarchy3:
Noconcatenate Load
[Hierarchy-1 ID],
BPAccount,
NumberHierarchy,
TotalSumSoldTo,
AccGrpID,
TotalSumOrderCr,
If(TotalSumOrderCr<6, 'A',
 If(TotalSumOrderCr<12, 'B',
 If(TotalSumOrderCr>21, 'C'))) as AOF
Resident TB1Hierarchy2;
Drop Table TB1Hierarchy2;
Rename Table TBHierarchy3 To TBHierarchy2;

[#5 · Accepted solution]

Hi,

Thausend Thanks.

it works perfekt.