topic Re: Creating a new field in the script using massive calculations from several fields in QlikView

Creating a new field in the script using massive calculations from several fields

alec1982 — Wed, 20 Jun 2012 18:04:22 GMT

Hi,

I have the following table that shows a schedule of payements

ID Payement Date Amount Most Recent payement

1 3/1/2012 800 1000

1 5/1/2012 1000 1000

1 6/1/2012 1000 1000

1 10/1/2012 1000 1000

2 1/1/2010 300 500

2 6/1/2012 500 500

2 8/1/2012 500 500

3 4/1/2011 300 1000

3 5/1/2012 1000 1000

3 12/1/2012 1000 1000

I need to calculate a new field to show the most recent payement.(The red field ).

the calculation should look at today's date and compare it to the the date field(Month/Year) and then put the amount of that row in a new field next to the ID.

I Am not sure how to do it..and i have' been thinking about it all day.

I appreciate any help..

Thxs,

Re: Creating a new field in the script using massive calculations from several fields

alec1982 — Wed, 20 Jun 2012 18:37:59 GMT

In other words,

I don't mind having a table with

ID Date Most recent Payement

1 6/1/2012 1000

2 6/1/2012 500

3 5/1/2012 1000

Thxs,

Re: Creating a new field in the script using massive calculations from several fields

Jason_Michaelides — Wed, 20 Jun 2012 19:22:50 GMT

Does the answer need to be based on current selections?

Re: Creating a new field in the script using massive calculations from several fields

alec1982 — Wed, 20 Jun 2012 19:33:43 GMT

Hi,

Not necessarily.

If i could get it without current selection then it is fine.

I also have a field that i would like to filter by.

The filter field name is xyz and the value that i am looking to filter by is A.

Thanks so much for your help,

Alec

Re: Creating a new field in the script using massive calculations from several fields

Jason_Michaelides — Wed, 20 Jun 2012 19:36:47 GMT

If that filter is user-applied then you can't do it in the script.

Have you tried using FirstSortedValue()? I think you'll need to use Aggr() in the second parameter.

Jason

Re: Creating a new field in the script using massive calculations from several fields

alec1982 — Wed, 20 Jun 2012 19:57:54 GMT

The filter is not user- applied.

Not sure how to do that.

Thxs,

Re: Creating a new field in the script using massive calculations from several fields

Jason_Michaelides — Wed, 20 Jun 2012 20:42:54 GMT

Try:

LOAD

ID,

Date,

LastValue(Payment) AS LastPayment

Resident PreviousTable

GROUP BY ID,Date

ORDER BY ID, Date;

Hope this helps,

Jason

Re: Creating a new field in the script using massive calculations from several fields

Jason_Michaelides — Wed, 20 Jun 2012 20:45:23 GMT

Actually I think that's slightly wrong:

LOAD

ID,

LastValue(Date) AS Date,

LastValue(Payment) AS LastPayment

Resident PreviousTable

GROUP BY ID

ORDER BY ID, Date;

Re: Creating a new field in the script using massive calculations from several fields

alec1982 — Wed, 20 Jun 2012 20:55:42 GMT

Hi,

Last value will bring the last date value which is 10/1/2012 in ID 1

I am looking for the most recent which should bring 6/1/2012.

Thxs

Re: Creating a new field in the script using massive calculations from several fields

Jason_Michaelides — Wed, 20 Jun 2012 21:08:33 GMT

ORDER BY ID, Date DESC

Re: Creating a new field in the script using massive calculations from several fields

alec1982 — Wed, 20 Jun 2012 21:22:34 GMT

I am getting an error "field not found"

not sure what's causing the error

but on the main load i have:

load

ID as IHS ID

Payment Date AS Due Date

Amount As Due Amount

and i have tried to do it the same as you sent me.

Thxs,

Re: Creating a new field in the script using massive calculations from several fields

Jason_Michaelides — Wed, 20 Jun 2012 22:45:01 GMT

temp:

LOAD ID,

[Payment Date],

Amount

FROM....;

Data:

LOAD

,LastValue([Payment Date])

,LastValue(Amount)

RESIDENT temp

WHERE [Payment Date] <= Today()

GROUP BY ID ORDER BY [Payment Date];

DROP TABLE temp;

See attached.

Re: Creating a new field in the script using massive calculations from several fields

alec1982 — Thu, 21 Jun 2012 19:29:09 GMT

Thxs man.

it worked perfectly.

Re: Creating a new field in the script using massive calculations from several fields

alec1982 — Fri, 22 Jun 2012 17:41:02 GMT

Hi again,

I still have an issue and wish you can help me with.

on the script that you sent me I am trying to add more fields and i am getting an error...

I am trying to add a calculated field of (AnnualAccurancy) *(Most recent Amount)..

the first field (Annual Accurancy ) is available in the first table and the second one is the lastvalue(MostRecentAmount) from the second table..

I appreciate your help.

Thxs,

Re: Creating a new field in the script using massive calculations from several fields

Jason_Michaelides — Fri, 22 Jun 2012 18:39:38 GMT

Then you most likely need to join the tables together first. This is probably best as a new post.