Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Problem with a loop through records

Hi,

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

I have a table similar to this :

YearDocCountryAccountAmount
2019123DEA-20
2019123DEB50
2019124DEB-2
2018125HUB10

 

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 "+")  :

YearDocCountryAccountAmountIndicator
2019123DEA-20+
2019123DEB50+
2019124DEB-2-
2018125HUB10+

 

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!

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Re: Problem with a loop through records

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;

 

View solution in original post

3 Replies
Highlighted
Creator
Creator

Re: Problem with a loop through records

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;

 

View solution in original post

Highlighted
Specialist III
Specialist III

Re: Problem with a loop through records

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;

 

Highlighted
Contributor
Contributor

Re: Problem with a loop through records

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!!