Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
G'day,
I have a table that show payment_date, ammount, payment_number, first_payment_ammount, other_payments_ammount
for example: payment_date = 28-APR-2013, ammount = 1000, payment_number = 9, first_payment_ammount = 200, other_payments_ammount = 100.
I want to to see each payment with it's calculated payment_date.
It's should look like this:
28-APR-2013, 200, 1
28-MAY-2013, 100, 2
28-JUN-2013, 100, 3
28-JUL-2013, 100, 4
28-AUG-2013, 100, 5
28-SEP-2013, 100, 6
28-OCT-2013, 100, 7
28-NOV-2013, 100, 8
28-DEC-2013, 100, 9
What's the best way to do it?
Thanks in advance
Yaniv
Try this:
Result:
NoConcatenate
LOAD DISTINCT
AddMonths( payment_date , IterNo()-1 ) as payment_date ,
If( IterNo()=1, first_payment_ammount, other_payments_ammount ) as payment ,
IterNo() as payment_number
RESIDENT <your_table>
WHILE IterNo() <= payment_number ;
Hi,
Did not get you.??
hi
try this
payment_date & ' , ' & first_payment_ammount & ', ' & payment_number
I have 1 record in my sales DB that shows the payment date and the number of payments.
I want to break it so each payment will have it's own record with a unique payment date (each payment is addition of 1 month from the original date) for example if the original sale was on the 01/04/2013 and there was 4 payments I want to see it in 4 records
01/04/2013
01/05/2013
01/06/2013
01/07/2013
Thanks for your reply
I am trying to change the original payment_date to a calculated one (according to the number of payments) so each payment will show an additional month from the original paymet date.
Thanks
Try this:
Result:
NoConcatenate
LOAD DISTINCT
AddMonths( payment_date , IterNo()-1 ) as payment_date ,
If( IterNo()=1, first_payment_ammount, other_payments_ammount ) as payment ,
IterNo() as payment_number
RESIDENT <your_table>
WHILE IterNo() <= payment_number ;
Thanks alot Richard
That did the job