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