Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
egoziyan
Contributor II
Contributor II

deployment of dates

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

1 Solution

Accepted Solutions
rlp
Creator
Creator

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 ;

View solution in original post

6 Replies
sujeetsingh
Master III
Master III

Hi,

Did not get you.??

Not applicable

hi

try this

payment_date & ' ,  ' &  first_payment_ammount  & ', ' &  payment_number

egoziyan
Contributor II
Contributor II
Author

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

egoziyan
Contributor II
Contributor II
Author

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

rlp
Creator
Creator

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 ;

egoziyan
Contributor II
Contributor II
Author

Thanks alot Richard

That did the job