Loop though table in script to make calculation

[annanirvin]

Hi.

Let's say we have a table like Table below and a variable with the value 5. Is it possible to loop through the table to reduce the value of Qty by one per row so that Qty for ID 1 = 22, ID 2 = 33, ID 3 = 55, ID 4 = 21 and ID 5 = 33? I want to do the calculation in the script.

Table:

LOAD * INLINE [

   ID, Qty

   1, 23

   2, 34

   3, 56

   4, 22

   5, 34

   6, 21

];

[Vegar · Accepted solution]

The variable = 5 does it limit which row you want to adjust? If so, do like this:

LET vVariable= 5;

Table:
LOAD 
  ID,
  //Qty as [Original Qty],
  if(ID>=$(vVariable), Qty, Qty-1) as Qty 
INLINE [
   ID, Qty
   1, 23
   2, 34
   3, 56
   4, 22
   5, 34
   6, 21
];

[aravind_dussani]

Hi Annanirvin,

Can you please expand your requirement with the example dataset of minimum 15values n please share both the input table & output table screens.

Thanks.

[jaibau1993 · Accepted solution]

Hi! there is no need for a loop. I understood that your variable controls the number of the (first) rows which Value must be sustracted by 1 (so I won't use ID). The following code worked for me:

LET RowsToReduce = 5;

OriginalTable:
LOAD * INLINE [
    ID, Value
    1, 1
    2, 2
    3, 3
    4, 4
    5, 5
    6, 6
    7, 7
    8, 8
    9, 9
    10, 10
];

ModififiedTable:
NoConcatenate LOAD
	ID,
	if(RecNo() <= $(RowsToReduce), Value-1, Value) as Value
Resident OriginalTable;

DROP Table OriginalTable;

Bests,

Jaime.

[Vegar]

Mine and @jaibau1993  suggested solutions will give you a similar output.  The big difference is if you want to do the manipulation IF(...) when you load the data from the source or if you want to adjust it after it is loaded. 

If we've understood your problem correctly, please mark one (or both) as an accepted solution, if not then please elaborate your problem and we could try to adjust our suggestion to your needs. 

-Vegar

[annanirvin]

Thanks for your suggestions and forgive my late reply, I'm busy in meetings all day. I'll try the suggestions as soon as I can and feed back to you then.

Kind regards
Anna

[annanirvin]

Finally, I have had the opportunity to test your solutions. They do the job excellently, both of them. Thank you so much for your help! 🙂

Regards,
Anna