Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try it with concat(comment, ',')
- Marcus
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)
Yes, correct. For each JobName the last comment.
what does the ',' do?
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?
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
Firstsortedvalue only works if txtnr = integer field. This is not the case... Or can I write the values into integer?
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
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?