Problem with Pivot

[#1]

Hi Experts

I have the following problem:

I would like to show a comment field in a Pivot Table. The dimension of the Pivot is the Jobname. For each Jobname there are several comments that get a continous number. I am able to distill the maximum number for each job (MaxNumber). How can I link that number to the respective comment field in the Pivot formula?

Thank you for your help!

Jan

[marcus_sommer]

Try it with concat(comment, ',')

- Marcus

[rubenmarin]

Hi Jan, I'm not sure to understand the requirements. You want for each JobName his last comment?

If you have a table like:

LOAD * Inline [

JobName, CommentNo, Comment

Test, 1, Comment1

Test, 2, Comment2

Test, 3, Comment3

];

You can show the last Comment for job with the expression:

FirstSortedValue(Comment, -CommentNo)

If you want this as dimension you can use:

=Aggr(FirstSortedValue(Comment, -CommentNo), JobName)

[#4]

Yes, correct. For each JobName the last comment.

[#5]

what does the ',' do?

[#6]

Okay, I understand. You concatenate all the entries into the filed. Now I would only have to read out from the last ','. How would I have to script this?

[marcus_sommer]

The ',' is the delimiter from concat - you could also use any other maybe chr(10) - and the function will return all comments to the jobname. If you want only the last then use the suggestion from Ruben with firtsortedvalue().

- Marcus

[#8]

Firstsortedvalue only works if  txtnr = integer field. This is not the case... Or can I write the values into integer?

[marcus_sommer]

If your comment-field isn't linked to a date-field you have to create an additionally numeric value within the script, maybe per rowno() and a proper sorting.

- Marcus

[rubenmarin]

Hi Jan, and how you identify wich is the last comment for each JobName? If it's by loading order you can follow Marcus advise and create a RowNo(), if not, can you post a sample data and explain how to identify the last comment?