topic Re: looping in qlikview in QlikView

looping in qlikview

Mon, 21 Apr 2014 13:48:26 GMT

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

Re: looping in qlikview

Tue, 22 Apr 2014 04:32:46 GMT

can any one suggest possible solution... is this possible?

Regards,

prajna

Re: looping in qlikview

vijetas42 — Tue, 22 Apr 2014 04:39:51 GMT

Hi,

can you more elaborate your requirement with example

Re: looping in qlikview

Tue, 22 Apr 2014 04:59:32 GMT

Hi,

I have balance table with na\balance amt=500

this amt will be for current date - 22 apr

now i need to find balance w.r.t 1 st apr

then

i need to need to iterate from current date to 19th april

i have transaction table where i have net balance with date now i need to sub balancve from balance table with net balance of transaction table.

balance table:

balance amt

200

Transaction table:

Date net balance

21apr 100

20 apr 200

18 apr -100

showcase like below

22 apr- 200 Balance

21 apr 200-0= 20(total balance -net balance on 22 apr)

20 apr 200-100= 100

19 apr 100-200= -100

18 apr -100-0= -100

so i can tell... on 18th by balance was -100

on 19th no transaction so blace remain same -100

on 20th 200 rs got credited so now balance is -100=200=100

on 21 apr 100 got credited so balance is 100+100=200

on 22 ap no transaction balance is 200

whenever i need to compute i should go from current date

Regards,

Prajna

Re: looping in qlikview

Tue, 22 Apr 2014 06:50:35 GMT

Hi Prajna, hope it can help you.

Balance:

LOAD

INLINE

[Balance Amt

200

];

Transaction:

LOAD

INLINE

[

Date1,Net balance

2014-04-21,100

2014-04-20,200

2014-04-18,-100

];

LET vBalanceAmt = Peek('Balance Amt',0,'Balance');

LET vCurrentDate = NUm(Date#(Date(Now(),'YYYY-MM-DD'),'YYYY-MM-DD'))+1;

Let vNumberOfRows = NoOfRows('Transaction');

Let vNetbalance = 0;

For vI = 0 to $(vNumberOfRows) - 1

Let vDate = num(Date#(Peek('Date1',vI,'Transaction'),'YYYY-MM-DD'));

Let vNetbalance = Peek('Net balance',vI,'Transaction');

TRACE $(vCurrentDate),$(vDate);

Result:

LOAD

Date($(vCurrentDate)-RecNo(), 'YYYY-MM-DD') AS Date2,

$(vBalanceAmt)-if($(vCurrentDate)-RecNo()+1 = $(vDate),$(vNetbalance),0) AS Balance

AutoGenerate($(vCurrentDate)-$(vDate))

;

LET vBalanceAmt = $(vBalanceAmt)-$(vNetbalance);

LET vCurrentDate = $(vDate);

Re: looping in qlikview

Tue, 22 Apr 2014 07:47:15 GMT

em getting this error..

Regards,

Prajna

Re: looping in qlikview

Tue, 22 Apr 2014 08:02:24 GMT

seems you modified some place. looks like your vDate is not correct.

Re: looping in qlikview

Tue, 22 Apr 2014 08:05:53 GMT

getting this error after modification

Regards,

Prajan

Re: looping in qlikview

Tue, 22 Apr 2014 09:01:26 GMT

is that possible to get your sample qvw?

BTW, I noticed that seems 41670 - 41673 make you wrong.

Re: Re: looping in qlikview

Tue, 22 Apr 2014 09:31:27 GMT

HI,

Suggest solution asap

Thank you

Regards,

Prajna

Re: Re: looping in qlikview

Tue, 22 Apr 2014 09:37:55 GMT

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?

Re: Re: looping in qlikview

Tue, 22 Apr 2014 09:47:17 GMT

Hi,

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

Though i change the order em facing same error.

Regards,

Prajna

Re: Re: Re: looping in qlikview

Wed, 23 Apr 2014 03:10:20 GMT

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

Re: looping in qlikview

MarcoWedel — Wed, 23 Apr 2014 23:30:49 GMT

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