Problem with a loop through records

[1991asia]

Hi,

I have a problem with creating a loop through the documents.

I have a table similar to this :

Year	Doc	Country	Account	Amount
2019	123	DE	A	-20
2019	123	DE	B	50
2019	124	DE	B	-2
2018	125	HU	B	10

I need to assign a new field to each record, which would indicate if the amount in positive or negative, however, since I can have a document split over the account field, I need to assign it based to aggregated amount.

First, I have created a key by combining Year, Doc and Country, which works OK.

Then, based on that I'd need a loop to find the key, and sum up all the rows for the key and then assign + or - to it based on value.

As a result it should show like this (where the first two lines have the same key 2019123DE, and despite negative amount on one of the records, the total is 30,so both of records are "+")  :

Year	Doc	Country	Account	Amount	Indicator
2019	123	DE	A	-20	+
2019	123	DE	B	50	+
2019	124	DE	B	-2	-
2018	125	HU	B	10	+

I have tried to write a code, that looks like this, but I have an Indicator as a variable and cannot load it into the app, since it's giving me error, so I cannot even check, whether it works :

Load
*,
'$(Indicator)' as Indicator,
Doc,
Country,
;



For Each key in FileList('[raw data]')

if Sum(Amount)<0 then
Indicator = '+
else
Indicator='-'
End If
next

Let key= Year&Doc&Country

load 
"Calendar year" as Year,
"Company code"as Country,
Doc.number as Doc,
Amount 
FROM [xxx]
(ooxml, embedded labels, table is [raw data]);

Could I kindly ask you for a help with that, if that's a good approach?

Are there other ways to do it?

Thank you!

[salezian · Accepted solution]

Hi

wouldn't be simpler to do it like this:

//grouping table 
tmp_table:
load 
   *,
   if(sum_amount)>0,'+','-') as indicator;
load 
   Year,
   Doc,
   Country,
   sum(Amount) as sum_amount
resident 
source_table
group by
   Year,
   Doc,
   Country;

result_table:
load 
   Year,
   Doc,
   Country,
   Amount
source_table
inner join(result_table) //join the source_table with table group table
load
   Year,
   Doc,
   Country,
Indicator
resident tmp_table;

[marcus_malinow]

Seems to me you may be overcomplicating this.

Load your original table then...

LEFT JOIN ([Your Table])

LOAD 

Account, 

if(sum(Amount)>=0, '+', '-') as Indicator

RESIDENT [Your Table]

GROUP BY Account;

[1991asia]

Hi,

I made a few correction and it worked :

tmp_table:
load 
   *,
   if(sum_amount>0,'+','-') as indicator;
load 
   Year,
   Doc,
   Country,
   sum(Amount) as sum_amount
resident 
raw_data
group by
   Year,
   Doc,
   Country;

result_table:
load 
   Year,
   Doc,
   Country,
   Amount
   
resident raw_data;
inner join(result_table) //join the source_table with table group table
load
   Year,
   Doc,
   Country,
indicator
resident tmp_table;

Thank you!!