Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
egoziyan
New 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
richard_pressan
Contributor

Re: deployment of dates

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 ;

6 Replies
sujeetsingh
Honored Contributor III

Re: deployment of dates

Hi,

Did not get you.??

Not applicable

Re: deployment of dates

hi

try this

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

egoziyan
New Contributor II

Re: deployment of dates

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
New Contributor II

Re: deployment of dates

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

richard_pressan
Contributor

Re: deployment of dates

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
New Contributor II

Re: deployment of dates

Thanks alot Richard

That did the job

Community Browser