topic Return Last String in field - using expression in QlikView

Return Last String in field - using expression

Fri, 18 Mar 2011 13:24:06 GMT

I want to return the last string in a field of data. What expression would i use to do that?

e.g.

Cost

100

200

170

the expression i want would return 170

Thanks In Advance

Fri, 18 Mar 2011 14:01:20 GMT

Hi, try this one:

=FieldValue('Cost',FieldValueCount('Cost'))

it is really important that you use single quotations. i've tested it on a text object expression

Fri, 18 Mar 2011 14:09:19 GMT

Apologies. that was unclear, your response works to a certain extent.

i am looking to group rows of data with the same reference ( see below )

Ref Cost

1 100

1 200

1 170

2 150

2 160

2 200

Would return

1 170

2 200

spividori — Fri, 18 Mar 2011 14:27:09 GMT

Hi!

See the example.

Regards.

Fri, 18 Mar 2011 14:32:38 GMT

Hello MITeamEldon,

I'm not quite certain if you want it in the script or in a sheetobject.
But here is my suggestion for a script:

//testdata:
tbl:
LOAD * INLINE [
Ref, Cost
1, 100
1, 200
1, 170
2, 150
2, 160
2, 200
];

//now load the data again,
//and find the last value of Cost per Ref
tblsum:
load
Ref,
LastValue(Cost)
resident tbl
group by Ref;

drop table tbl;

Best regards