topic Re: Problem with Pivot in QlikView

Problem with Pivot

Tue, 10 Mar 2015 16:03:18 GMT

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

Re: Problem with Pivot

marcus_sommer — Tue, 10 Mar 2015 16:24:47 GMT

Try it with concat(comment, ',')

- Marcus

Re: Problem with Pivot

rubenmarin — Tue, 10 Mar 2015 16:26:16 GMT

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)

Re: Problem with Pivot

Tue, 10 Mar 2015 16:31:19 GMT

Yes, correct. For each JobName the last comment.

Re: Problem with Pivot

Tue, 10 Mar 2015 16:34:49 GMT

what does the ',' do?

Re: Problem with Pivot

Tue, 10 Mar 2015 16:39:54 GMT

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?

Re: Problem with Pivot

marcus_sommer — Tue, 10 Mar 2015 16:41:20 GMT

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

Re: Problem with Pivot

Tue, 10 Mar 2015 16:44:22 GMT

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

Re: Problem with Pivot

marcus_sommer — Tue, 10 Mar 2015 16:50:38 GMT

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

Re: Problem with Pivot

rubenmarin — Wed, 11 Mar 2015 10:15:35 GMT

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?