topic Re: Loop though table in script to make calculation in QlikView

Loop though table in script to make calculation

annanirvin — Sun, 05 May 2019 17:03:47 GMT

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

];

Re: Loop though table in script to make calculation

Vegar — Mon, 06 May 2019 07:24:49 GMT

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 ];

Re: Loop though table in script to make calculation

aravind_dussani — Sun, 05 May 2019 21:19:55 GMT

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.

Re: Loop though table in script to make calculation

jaibau1993 — Mon, 06 May 2019 07:20:26 GMT

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.

Re: Loop though table in script to make calculation

Vegar — Mon, 06 May 2019 07:29:58 GMT

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

Re: Loop though table in script to make calculation

annanirvin — Mon, 06 May 2019 16:37:02 GMT

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

Re: Loop though table in script to make calculation

annanirvin — Mon, 13 May 2019 14:24:09 GMT

Re: Loop though table in script to make calculation

annanirvin — Mon, 13 May 2019 14:26:27 GMT

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