Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
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.
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;
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;
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;