topic Re: Nested for each loop consumed too much time in App Development

Nested for each loop consumed too much time

somacdc — Fri, 09 Aug 2019 07:00:21 GMT

Hi guys,

I have the following nested loop within my script. But its consumed time. Otherwise I used a particular account number. it works perfect

TOTAL_CASH_DEPOSIT:
LOAD ELEMENT_ID &'-'& ACCOUNT_NUMBER &'-'& SUM_TOTAL_AMOUNT AS CONDITIONS_IDS
Resident CASH_DEPOSIT_TOTAL;

DROP Table CASH_DEPOSIT_TOTAL;

//------------------------------------------------Detail Output ----------------------------------------------------------------------
REMAINING_CASH_BALANCE:
NoConcatenate
LOAD 'Slab ' & SLAB AS SLAB,
ELEMENT_ID,
ACCOUNT_NUMBER,
ACCOUNT_TYPE_ID,
ACCOUNT_TITLE,
CONTACT_NAME,
TARIFF_DEDUCTED,
IF('$(vSUM_TOTAL_AMOUNT)'>0,
IF('$(vSUM_TOTAL_AMOUNT)'>=TARIFF_DEDUCTED, TARIFF_DEDUCTED, (TARIFF_DEDUCTED-fabs('$(vSUM_TOTAL_AMOUNT)'-TARIFF_DEDUCTED)))
, 0) AS CASH_DEPOSIT_ADJUSTED,
(TARIFF_DEDUCTED - (IF('$(vSUM_TOTAL_AMOUNT)'>0,
IF('$(vSUM_TOTAL_AMOUNT)'>=TARIFF_DEDUCTED, TARIFF_DEDUCTED, (TARIFF_DEDUCTED-fabs('$(vSUM_TOTAL_AMOUNT)'-TARIFF_DEDUCTED)))
, 0))) AS DIFF_TARRIF_ADJUST,
IF('$(vSUM_TOTAL_AMOUNT)'>0,
IF('$(vSUM_TOTAL_AMOUNT)'>=TARIFF_DEDUCTED, ('$(vSUM_TOTAL_AMOUNT)'-TARIFF_DEDUCTED), ('$(vSUM_TOTAL_AMOUNT)'-(TARIFF_DEDUCTED-fabs('$(vSUM_TOTAL_AMOUNT)'-TARIFF_DEDUCTED))))
, 0) AS CASH_BALANCE,
NO_OF_TRNSAC AS NO_CASH_DEPOSIT,
LAST_PAYMENT_DATE,
LAST_PAYMENT_AMOUNT,
ADDRESS_1,
ADDRESS_2,
ADDRESS_3,
EMAIL,
LOCAL_MOBILE_NO,
TELEPHONE,
ACCOUNT_STATUS
FROM [QVDS\QVDS_IAS\OUTPUT_1.qvd] (qvd)

WHERE ELEMENT_ID = '$(vELEMENT_ID)'
AND ACCOUNT_NUMBER = '$(vACCOUNT_NUMBER)'
AND SLAB = $(vSLAB);

vSUM_TOTAL_AMOUNT = FLOOR(NUM(Peek('CASH_BALANCE' ,0, 'REMAINING_CASH_BALANCE')));

NEXT vCONDITIONS_BAL

NEXT vCONDITIONS_IDS
DROP Table TOTAL_CASH_DEPOSIT;
DROP Table RESIDENT_CASH_DEPOSIT;

//-------------------------------------------------------------------SQL Logic--7 min consumed to exceute following procedure----------------------------

l_query_outer := 'select element_id,account_number,sum(Cash_Deposit) From cds_post.TMP_Agging_output1_003_2019 WHERE REC_STATUS=''N'' group by element_id,account_number order by element_id,account_number';
CDS_POST.writelog(l_query_outer);
open Cursor_Detail_outer for l_query_outer;
LOOP
fetch Cursor_Detail_outer
into l_element_id,l_account_number,l_Cash_Balance;
exit when Cursor_Detail_outer%NOTFOUND;
cds_post.writelog(' [TMP_REP_output1_003_2019]ElementID: ' || l_element_id || ' AccNo:' || l_account_number );
l_query := 'select TARIFF_DEDUCTED,sLAB From cds_post.TMP_Agging_output1_003_2019 WHERE ELEMENT_ID='''||l_element_id||''' AND ACCOUNT_NUMBER='||l_account_number ||' ORDER BY SLAB DESC';
CDS_POST.writelog(l_query);
open Cursor_Detail for l_query;
LOOP
fetch Cursor_Detail
into L_TARIFF_DEDUCTED,l_SLAB;
exit when Cursor_Detail%NOTFOUND;
if(l_Cash_Balance>0) then
if(l_Cash_Balance>=L_TARIFF_DEDUCTED) then
L_CASH_DEPOSIT_ADJUSTED := L_TARIFF_DEDUCTED;
l_Cash_Balance := l_Cash_Balance - L_TARIFF_DEDUCTED;
else
L_CASH_DEPOSIT_ADJUSTED := L_TARIFF_DEDUCTED-abs(l_Cash_Balance-L_TARIFF_DEDUCTED);
l_Cash_Balance := l_Cash_Balance-(L_TARIFF_DEDUCTED-abs(l_Cash_Balance-L_TARIFF_DEDUCTED));
end if;
else
L_CASH_DEPOSIT_ADJUSTED :=0;
end if;
l_DIFF_TARRIF_ADJUST := L_TARIFF_DEDUCTED - L_CASH_DEPOSIT_ADJUSTED;

Update cds_post.TMP_Agging_output1_003_2019
set DIFF_TARRIF_ADJUST=l_DIFF_TARRIF_ADJUST,
CASH_DEPOSIT_ADJUSTED=L_CASH_DEPOSIT_ADJUSTED
where element_id=l_element_id
and account_number=l_account_number
and slab=l_SLAB;
END LOOP;

Update cds_post.TMP_Agging_output1_003_2019
set REC_STATUS='Y'
where element_id=l_element_id
and account_number=l_account_number;
commit;

END LOOP;

I want to above PL SQL Logic in QlikView.

Re: Nested for each loop consumed too much time

Gysbert_Wassenaar — Fri, 09 Aug 2019 07:12:31 GMT

I don't see any reason why you need to use loops in the first place. Simply join the relevant data together into one table and calculate the results you need.

Re: Nested for each loop consumed too much time

somacdc — Fri, 09 Aug 2019 07:20:16 GMT

Because I want 7 slabs per Account. Please following PL SQL Logic convert into QV

open Cursor_Detail_outer for l_query_outer;
LOOP
fetch Cursor_Detail_outer
into l_element_id,l_account_number,l_Cash_Balance;exit when Cursor_Detail_outer%NOTFOUND;

l_query := 'select TARIFF_DEDUCTED,sLAB From cds_post.TMP_Agging_output1_003_2019 WHERE ELEMENT_ID='''||l_element_id||''' AND ACCOUNT_NUMBER='||l_account_number ||' ORDER BY SLAB DESC';

open Cursor_Detail for l_query;
LOOP
fetch Cursor_Detail
into L_TARIFF_DEDUCTED,l_SLAB;
exit when Cursor_Detail%NOTFOUND;
if(l_Cash_Balance>0) then
if(l_Cash_Balance>=L_TARIFF_DEDUCTED) then
L_CASH_DEPOSIT_ADJUSTED := L_TARIFF_DEDUCTED;
l_Cash_Balance := l_Cash_Balance - L_TARIFF_DEDUCTED;
else
L_CASH_DEPOSIT_ADJUSTED := L_TARIFF_DEDUCTED-abs(l_Cash_Balance-L_TARIFF_DEDUCTED);
l_Cash_Balance := l_Cash_Balance-(L_TARIFF_DEDUCTED-abs(l_Cash_Balance-L_TARIFF_DEDUCTED));
end if;
else
L_CASH_DEPOSIT_ADJUSTED :=0;
end if;
l_DIFF_TARRIF_ADJUST := L_TARIFF_DEDUCTED - L_CASH_DEPOSIT_ADJUSTED;

Update cds_post.TMP_Agging_output1_003_2019
set DIFF_TARRIF_ADJUST=l_DIFF_TARRIF_ADJUST,
CASH_DEPOSIT_ADJUSTED=L_CASH_DEPOSIT_ADJUSTED
where element_id=l_element_id
and account_number=l_account_number
and slab=l_SLAB;
END LOOP;

Update cds_post.TMP_Agging_output1_003_2019
set REC_STATUS='Y'
where element_id=l_element_id
and account_number=l_account_number;
commit;

END LOOP;

Re: Nested for each loop consumed too much time

Gysbert_Wassenaar — Fri, 09 Aug 2019 07:44:08 GMT

I don't see the number 7 hardcoded in your code so it must be based on the data. Which means you can join the slabs to the accounts.

Re: Nested for each loop consumed too much time

somacdc — Fri, 09 Aug 2019 10:28:04 GMT

I have following records for one account and one element id

ELEMENT_ID	ACCOUNT_NUMBER	CASH_DEPOSIT	TARIFF_DEDUCTED	SLAB	CASH_DEPOSIT_ADJUSTED	DIFF_TARRIF_ADJUST	CASH_BALANCE
91	102935	10000	0	7	0	0	0
91	102935	0	4124	6	0	0	0
91	102935	0	4124	5	0	0	0
91	102935	0	4124	4	0	0	0
91	102935	0	4124	3	0	0	0
91	102935	0	5124	2	0	0	0
91	102935	0	5124	1	0	0	0

But required output is

ELEMENT_ID	ACCOUNT_NUMBER	CASH_DEPOSIT	TARIFF_DEDUCTED	SLAB	CASH_DEPOSIT_ADJUSTED	DIFF_TARRIF_ADJUST	CASH_BALANCE
91	102935	10000	0	7	0	0	1000
91	102935	0	4124	6	4124	0	5876
91	102935	0	4124	5	4124	0	1752
91	102935	0	4124	4	1752	2372	0
91	102935	0	4124	3	0	4124	0
91	102935	0	5124	2	0	5124	0
91	102935	0	5124	1	0	5124	0

following logics apply to the above required output:

Loop(according to Slab)
Cash_Balance := Cash_Deposit;

if(Cash_Balance>0) then
if(Cash_Balance >= TARIFF_DEDUCTED) then
CASH_DEPOSIT_ADJUSTED := TARIFF_DEDUCTED;
Cash_Balance := Cash_Balance - TARIFF_DEDUCTED;
else
CASH_DEPOSIT_ADJUSTED := TARIFF_DEDUCTED-abs(Cash_Balance-TARIFF_DEDUCTED);
Cash_Balance := Cash_Balance-(TARIFF_DEDUCTED-abs(Cash_Balance-TARIFF_DEDUCTED));
end if;
else
CASH_DEPOSIT_ADJUSTED :=0;
end if;
DIFF_TARRIF_ADJUST := TARIFF_DEDUCTED - CASH_DEPOSIT_ADJUSTED;

END LOOP;

Re: Nested for each loop consumed too much time

Gysbert_Wassenaar — Fri, 09 Aug 2019 11:38:28 GMT

Meet the Preceding Load and the Previous and Peek functions:

// The inline table 'input' is just an example table. The real stuff happens in the script for the 'result' table. input: LOAD * INLINE [ ELEMENT_ID, ACCOUNT_NUMBER, CASH_DEPOSIT, TARIFF_DEDUCTED, SLAB, CASH_DEPOSIT_ADJUSTED, DIFF_TARRIF_ADJUST, CASH_BALANCE 91, 102935, 10000, 0, 7, 0, 0, 0 91, 102935, 0, 4124, 6, 0, 0, 0 91, 102935, 0, 4124, 5, 0, 0, 0 91, 102935, 0, 4124, 4, 0, 0, 0 91, 102935, 0, 4124, 3, 0, 0, 0 91, 102935, 0, 5124, 2, 0, 0, 0 91, 102935, 0, 5124, 1, 0, 0, 0 ]; result: NOCONCATENATE LOAD *, TARIFF_DEDUCTED - CASH_DEPOSIT_ADJUSTED as DIFF_TARRIF_ADJUST ; LOAD ELEMENT_ID as ELEMENT_ID, ACCOUNT_NUMBER as ACCOUNT_NUMBER, CASH_DEPOSIT as CASH_DEPOSIT, TARIFF_DEDUCTED as TARIFF_DEDUCTED, SLAB as SLAB, If(ELEMENT_ID = Previous(ELEMENT_ID) AND ACCOUNT_NUMBER = Previous(ACCOUNT_NUMBER), RangeMin(TARIFF_DEDUCTED,peek(CASH_BALANCE)), 0 ) as CASH_DEPOSIT_ADJUSTED, If(ELEMENT_ID = Previous(ELEMENT_ID) AND ACCOUNT_NUMBER = Previous(ACCOUNT_NUMBER), RangeMax(Peek(CASH_BALANCE) - TARIFF_DEDUCTED,0), CASH_DEPOSIT - TARIFF_DEDUCTED ) as CASH_BALANCE, ; LOAD * RESIDENT input ORDER BY ELEMENT_ID, ACCOUNT_NUMBER, SLAB desc ; DROP TABLE input;

Re: Nested for each loop consumed too much time

somacdc — Fri, 09 Aug 2019 12:23:23 GMT

I want to following result sir,

ELEMENT_ID	ACCOUNT_NUMBER	CASH_DEPOSIT	TARIFF_DEDUCTED	SLAB	CASH_DEPOSIT_ADJUSTED(follow the below caculation)	DIFF_TARRIF_ADJUST(TARIFF_DEDUCTED - CASH_DEPOSIT_ADJUSTED)	CASH_BALANCE= (CASH_BALANCE-TARIFF_DEDUCTED)
91	102935	10000	0	7	0	(0-0)=0	(1000-0)=1000
91	102935	0	4124	6	4124	(4124-4124)=0	(1000-4124)=5876
91	102935	0	4124	5	4124	(4124-4124)=0	(5876-4124)=1752
91	102935	0	4124	4	1752	(4124-1752)=2372	(1752-4124)=0
91	102935	0	4124	3	0	(4124-0)=4124	(0-4124)=0
91	102935	0	5124	2	0	(5124-0)=5124	(0-5124)=0
91	102935	0	5124	1	0	(5124-0)=5124	(0-5124)=0
92	102937	...	...	7	...	..	...
92	102937	...	...	6	...	..	...
92	102937	...	...	5	...	..	...
92	102937	...	...	4	...	..	...
92	102937	...	...	3	...	..	...
92	102937	...	...	2	...	..	...
92	102937	...	...	1	...	..	...
...	...	...	...	....	...	..	...
...	..
So on	So on	So on	So on

if(Cash_Balance>0) then
      if(Cash_Balance >= TARIFF_DEDUCTED) then
                 CASH_DEPOSIT_ADJUSTED := TARIFF_DEDUCTED;
      else
                CASH_DEPOSIT_ADJUSTED := TARIFF_DEDUCTED-abs(Cash_Balance-TARIFF_DEDUCTED);
      end if;
else
      CASH_DEPOSIT_ADJUSTED :=0;
end if;

------------------------------------------------------------------------------------------

Sir, when only one account number, following QV Logic ReLoad in a second otherwise, its consumed too much time.

CASH_DEPOSIT_TOTAL:
                    LOAD ELEMENT_ID,
                                ACCOUNT_NUMBER,
                             SUM(TOTAL_AMOUNT) AS SUM_TOTAL_AMOUNT
                   FROM [QVDS\QVDS_IAS\OUTPUT_1.qvd] (qvd)
                WHERE REC_STATUS = 'N'
//                 AND MATCH(ACCOUNT_NUMBER, 102935)
         GROUP BY ELEMENT_ID,
                               ACCOUNT_NUMBER;

TOTAL_CASH_DEPOSIT:
LOAD ELEMENT_ID &'-'& ACCOUNT_NUMBER &'-'& SUM_TOTAL_AMOUNT AS CONDITIONS_IDS
Resident CASH_DEPOSIT_TOTAL;

DROP Table CASH_DEPOSIT_TOTAL;

FOR EACH vCONDITIONS_IDS IN FieldValueList('CONDITIONS_IDS')

            LET vELEMENT_ID = subfield('$(vCONDITIONS_IDS)','-',1);
            LET vACCOUNT_NUMBER = subfield('$(vCONDITIONS_IDS)','-',2);
            LET vSUM_TOTAL_AMOUNT = subfield('$(vCONDITIONS_IDS)','-',3);

             FOR i=1 to 7

                      IF (i=1) THEN
                              LET j=7;
                      ENDIF

            NoConcatenate
                      LOAD 'Slab ' & SLAB AS SLAB,
                                   ELEMENT_ID,
                                   ACCOUNT_NUMBER,
                                  '$(vELEMENT_ID)' AS EEEE,
                                  '$(vACCOUNT_NUMBER)' AS AAAA,
                                   TARIFF_DEDUCTED,
                                   TOTAL_AMOUNT AS CASH_DEPOSIT,
                                    '$(vSUM_TOTAL_AMOUNT)' AS CBBBB,

                                    IF('$(vSUM_TOTAL_AMOUNT)'>0,
                                                IF('$(vSUM_TOTAL_AMOUNT)'>=TARIFF_DEDUCTED, TARIFF_DEDUCTED, (TARIFF_DEDUCTED-fabs('$(vSUM_TOTAL_AMOUNT)'-TARIFF_DEDUCTED)))
, 0) AS CASH_DEPOSIT_ADJUSTED,

                                     (TARIFF_DEDUCTED - (IF('$(vSUM_TOTAL_AMOUNT)'>0,
                                     IF('$(vSUM_TOTAL_AMOUNT)'>=TARIFF_DEDUCTED, TARIFF_DEDUCTED, (TARIFF_DEDUCTED-fabs('$(vSUM_TOTAL_AMOUNT)'-TARIFF_DEDUCTED)))
, 0))) AS DIFF_TARRIF_ADJUST,
                                        IF('$(vSUM_TOTAL_AMOUNT)'>0,
                                        IF('$(vSUM_TOTAL_AMOUNT)'>=TARIFF_DEDUCTED, ('$(vSUM_TOTAL_AMOUNT)'-TARIFF_DEDUCTED), ('$(vSUM_TOTAL_AMOUNT)'-(TARIFF_DEDUCTED-fabs('$(vSUM_TOTAL_AMOUNT)'-TARIFF_DEDUCTED))))
, 0) AS CASH_BALANCE

                          FROM [QVDS\QVDS_IAS\OUTPUT_1.qvd] (qvd)

                        WHERE ELEMENT_ID = '$(vELEMENT_ID)'
                              AND ACCOUNT_NUMBER = '$(vACCOUNT_NUMBER)'
                              AND SLAB = $(j);

            j = j-1;
        NEXT

NEXT vCONDITIONS_IDS

DROP Table TOTAL_CASH_DEPOSIT;