Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Thank you very much!
Thanks for the detail answer. Coupons