looping in qlikview

[#1]

Hi All,

I have to loop through records.

My requiremnet is

i have to take balance amt from currenct date

and decrement date until required todate and calculate balance for each date until todate.

from date will be current date.

to calculate i have planned for this lgic dnt knw how to implement in qlikview need to display in chart balance for all dates.we only have data for currennt date balance remaining we neeed to calculate.

logic:

initially v_currentdate will be current date(from date) and v_Todate is to date

v_currentdate=

v_Newdate=v_currentdate-1

v_newbalance =balance of v_currentdate-net balance of v_currentdate

v_Newdate=v_Todate (if yes exit else)

v_currentdate=v_Newdate

can anyone help me n using this in qlikview

Regrads,

Prajna

[#2]

As I said 41670 - 41673 make you wrong, you may need to correct your script, may be exchange the two number's order.

It's hard to provide solution, no sure what's your data looks like, would you please provide some sample data?

[#3]

Hi,

I have already shared sample data can u help me out ?

Though i change the order em facing same error.

Regards,

Prajna

[#4]

Very sorry to reply late. Yesterday I am off work. Please see whether is helpful.

[MarcoWedel]

Hi Prajna,

try to avoid looping through data tables, if you can.

Maybe the following script helps you to create a solution without the need for any loops?

tabBank:
LOAD CODE,
     NAME
FROM http://community.qlik.com/servlet/JiveServlet/download/513135-102916/BANK.xlsx
(ooxml, embedded labels, table is Sheet1);

tabBalance:
LOAD ID,
     code  AS CODE,
     [Balance Amount]AS Balance_Loc,
     AutoNumberHash128(code, ID) as %CODEID
FROM http://community.qlik.com/servlet/JiveServlet/download/513135-102935/Balance.xlsx
(ooxml, embedded labels, table is Sheet1);

tabTransaction:
LOAD ID,
     LEBT_Date,
     Bankcode AS CODE,
     CREDIT,
     DEBIT,
     NET AS MainBalanceAmount,
     AutoNumberHash128(Bankcode, ID) as %CODEID
FROM http://community.qlik.com/servlet/JiveServlet/download/513135-102936/TRANSACTION.xlsx
(ooxml, embedded labels, table is Sheet1);

tabLink:
LOAD Distinct
    CODE,
    ID,
    %CODEID
Resident tabBalance;

Join (tabLink)
LOAD Distinct
    CODE,
    ID,
    %CODEID
Resident tabTransaction;

DROP Fields CODE, ID From tabBalance, tabTransaction;

tabBalanceHistory:
LOAD
    Date(MinBalanceDate+IterNo()-1) as BalanceDate
While MinBalanceDate+IterNo()-1 <= Today();
LOAD
    min(LEBT_Date) as MinBalanceDate
Resident tabTransaction;

Left Join (tabBalanceHistory)
LOAD
    %CODEID
Resident tabLink;

Left Join (tabBalanceHistory)
LOAD
    LEBT_Date as BalanceDate,
    %CODEID,
    Sum(MainBalanceAmount) as BalanceAmount
Resident tabTransaction
Group By LEBT_Date, %CODEID;

Left Join (tabBalanceHistory)
LOAD
    %CODEID,
    Balance_Loc as BalanceLoc
Resident tabBalance;

Left Join (tabBalanceHistory)
LOAD
    BalanceDate,
    %CODEID,
    if(%CODEID = Peek(%CODEID), NumSum(Peek(Balance), BalanceAmount), BalanceLoc) as Balance
Resident tabBalanceHistory
Order By %CODEID, BalanceDate desc;

DROP Fields BalanceAmount, BalanceLoc From tabBalanceHistory;

regards

Marco