topic Re: Data transformation alternatives using Qlikview in QlikView

Data transformation alternatives using Qlikview

Fri, 26 Apr 2013 16:06:03 GMT

Hi!, I need to load data to Qlikview making some transformation, I have the folowing table at oracle:

operation nbr	coupon	expiry date	amount
1001	1	4/31/13	100.00
1001	2	5/31/13	100.00
1001	3	6/31/13	100.00
1001	4	7/31/13	100.00
1001	5	8/31/13	100.00
1001	6	9/31/13	100.00

At the other hand, a have a balance value, which corresponds to the balance due (for example 600.00)

What I need is to load to Qlikview a table like below

operation nbr	coupon	expiry date	amount	balance due
1001	1	4/31/13	100.00	600.00
1001	2	5/31/13	100.00	500.00
1001	3	6/31/13	100.00	400.00
1001	4	7/31/13	100.00	300.00
1001	5	8/31/13	100.00	200.00
1001	6	9/31/13	100.00	100.00

I must to calculate the balance due month to month (balance - coupon amount). I cannot create an stored procedure at the system schema because of company policies, however I have to process record by record to calculate the balance due.

Exists a way to do that using Qlikview script language?

Thanks in advance!

Re: Data transformation alternatives using Qlikview

vivek_niti — Fri, 26 Apr 2013 16:25:50 GMT

Hi,

Try this code...

LOAD *,Balance_Amt - new_due_amt AS Final_Due_Amt;

LOAD [operation nbr],

coupon,

[expiry date],

amount,

600 AS Balance_Amt,

if(coupon = 1, 0,rangesum(amount,peek(new_due_amt))) AS new_due_amt

FROM....

Hope it helps...

Regards,

Vivek

Re: Data transformation alternatives using Qlikview

Fri, 26 Apr 2013 16:59:00 GMT

Thank you very much!

Re: Data transformation alternatives using Qlikview

Wed, 11 Sep 2013 17:59:01 GMT

Thanks for the detail answer. Coupons