Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Data transformation alternatives using Qlikview

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

operation nbr
coupon
expiry date
amount
100114/31/13100.00
100125/31/13100.00
100136/31/13100.00
100147/31/13100.00
100158/31/13100.00
100169/31/13100.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
100114/31/13100.00600.00
100125/31/13100.00500.00
100136/31/13100.00400.00
100147/31/13100.00300.00
100158/31/13100.00200.00
100169/31/13100.00100.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!

Tags (1)
1 Solution

Accepted Solutions
vivek_niti
Contributor

Re: Data transformation alternatives using Qlikview

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

3 Replies
vivek_niti
Contributor

Re: Data transformation alternatives using Qlikview

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

Not applicable

Re: Data transformation alternatives using Qlikview

Thank you very much!

Not applicable

Re: Data transformation alternatives using Qlikview

Thanks for the detail answer. Coupons

Community Browser