Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
somacdc
Contributor III
Contributor III

Nested for each loop consumed too much time

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

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,102239,102640,52,55) 
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);

TOTAL_CASH_BALANCE:
NoConcatenate
LOAD TARIFF_DEDUCTED,
SLAB,
'$(vELEMENT_ID)' AS EEEE,
'$(vACCOUNT_NUMBER)' AS AAAA
FROM [QVDS\QVDS_IAS\OUTPUT_1.qvd] (qvd)
WHERE ELEMENT_ID = '$(vELEMENT_ID)'
AND ACCOUNT_NUMBER = '$(vACCOUNT_NUMBER)';

RESIDENT_CASH_DEPOSIT:
LOAD TARIFF_DEDUCTED &'-'& SLAB AS DEDUCTION_WITH_SLAB
Resident TOTAL_CASH_BALANCE
ORDER BY SLAB DESC;

DROP Table TOTAL_CASH_BALANCE;

FOR EACH vCONDITIONS_BAL IN FieldValueList('DEDUCTION_WITH_SLAB')
LET vTARIFF_DEDUCTED = subfield('$(vCONDITIONS_BAL)','-',1);
LET vSLAB = subfield('$(vCONDITIONS_BAL)','-',2);

//------------------------------------------------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.

6 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
somacdc
Contributor III
Contributor III
Author

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

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

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;

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
somacdc
Contributor III
Contributor III
Author

I have following records for one account and one element id

ELEMENT_IDACCOUNT_NUMBERCASH_DEPOSITTARIFF_DEDUCTEDSLABCASH_DEPOSIT_ADJUSTEDDIFF_TARRIF_ADJUSTCASH_BALANCE
911029351000007000
91102935041246000
91102935041245000
91102935041244000
91102935041243000
91102935051242000
91102935051241000

 

But required output is

ELEMENT_IDACCOUNT_NUMBERCASH_DEPOSITTARIFF_DEDUCTEDSLABCASH_DEPOSIT_ADJUSTEDDIFF_TARRIF_ADJUSTCASH_BALANCE
911029351000007001000
91102935041246412405876
91102935041245412401752
91102935041244175223720
91102935041243041240
91102935051242051240
91102935051241051240

 

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;

Gysbert_Wassenaar

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;

 

 

 


talk is cheap, supply exceeds demand
somacdc
Contributor III
Contributor III
Author

I want to following result sir,

ELEMENT_IDACCOUNT_NUMBERCASH_DEPOSITTARIFF_DEDUCTEDSLABCASH_DEPOSIT_ADJUSTED(follow the below caculation)DIFF_TARRIF_ADJUST(TARIFF_DEDUCTED - CASH_DEPOSIT_ADJUSTED)CASH_BALANCE= (CASH_BALANCE-TARIFF_DEDUCTED)
9110293510000070(0-0)=0(1000-0)=1000
911029350412464124(4124-4124)=0(1000-4124)=5876
911029350412454124(4124-4124)=0(5876-4124)=1752
911029350412441752(4124-1752)=2372(1752-4124)=0
911029350412430(4124-0)=4124(0-4124)=0
911029350512420(5124-0)=5124(0-5124)=0
911029350512410(5124-0)=5124(0-5124)=0
92102937......7........
92102937......6........
92102937......5........
92102937......4........
92102937......3........
92102937......2........
92102937......1........
........................
.....      
So onSo onSo onSo 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;