topic Re: Pivot Query in QlikView

Pivot Query

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i have data in the below format:

Task	Comments	Date
BBPLC for Swap	01 Oct: - Migration of Non US Population off BCSL	10/20/2016
BBPLC for Swap	01 Oct: - Migration of Non US Population off BCSL	10/19/2016
BCI -Enhancement	12 Oct: -House Position and House ladder protype ready -Actuals Vs Projections Reconciliation and MTM prototype ready	10/20/2016
BCI -Enhancement	12 Oct: -House Position and House ladder protype ready -Actuals Vs Projections Reconciliation and MTM prototype ready	10/19/2016

and i want to create a pivot table which will display the result as the below format:

Task	Comments	Date
BBPLC for Swap	01 Oct: - Migration of Non US Population off BCSL	10/20/2016
BBPLC for Swap	01 Oct: - Migration of Non US Population off BCSL	10/19/2016
BCI -Enhancement	12 Oct: -House Position and House ladder protype ready -Actuals Vs Projections Reconciliation and MTM prototype ready	10/20/2016
BCI -Enhancement	12 Oct: -House Position and House ladder protype ready -Actuals Vs Projections Reconciliation and MTM prototype ready	10/19/2016

Where Date is sorted with recent date and Date column is hidden

please advise

thanks

Sukhwant

Re: Pivot Query

Fri, 21 Oct 2016 13:33:37 GMT

Any update on this please

Re: Pivot Query

MK9885 — Fri, 21 Oct 2016 13:37:57 GMT

Can you attach the sample data?

Re: Pivot Query

Fri, 21 Oct 2016 13:52:42 GMT

its there in the body. feature not active to upload info

thanks

Re: Pivot Query

Anonymous — Fri, 21 Oct 2016 14:11:43 GMT

The pivot table should have dimensions Task and Comments, where Comments is sorted by expression based on Date.
You still need an expression for the table, at least a "blank expression".

Re: Pivot Query

MK9885 — Fri, 21 Oct 2016 14:46:30 GMT

Pivot table works with drop down select.

So if you add Task and Comments as dimension.

Use if condition Date as Calculated Dimension if you want to hide dates.

But you always need to have at least 1 expression to populate the pivot table.

Re: Pivot Query

Frank_Hartmann — Sun, 23 Oct 2016 14:03:05 GMT

what about this solution?

hope this helps

Re: Pivot Query

Anil_Babu_Samineni — Sun, 23 Oct 2016 18:05:46 GMT

I am not sure if you are looking this, But hope this might you are expecting

So that, you can create Pivot Table with the Dim of Task, Date

Expression is : SubField(Comments,'-',1) & Chr(10) & '-' & SubField(Comments,'-',-1)

You can use Dimensions are Task, Date

Expressions are : 1) SubField(Comments,'-',1) & Chr(10) & '-' & SubField(Comments,'-',-1)

2) Date

Then, you can create one Macro to hide the Dimension like below

FUNCTION HideColumn(ch, n)
set ch = ActiveDocument.GetSheetObject(ch)
ch.SetPixWidth (n-1),0
set ch = Nothing
END FUNCTION

SUB ColHide
call HideColumn("CH01", 2)
END SUB

sub kache
ActiveDocument.ClearCache
MSGBOX "OK Cache"
END SUB

After This, you will get this Image

Re: Pivot Query

vardhancse — Mon, 24 Oct 2016 03:13:58 GMT

Hi,

Load the data into QV.

Create one Pivot table:

1. Table Pivot

2. dimension(Task, Comments)

3. Expression(Only(Date))

Now Pivot table will be as expected.

Coming to Sort, Double click on the Date column for sorting as per the requirement.

Re: Pivot Query

Mon, 24 Oct 2016 07:04:14 GMT

excellent thanks a lot.

But require just one more thing, if for different dates the comments are the same, so want to show only 1 comments,

how can we do that

Re: Pivot Query

Frank_Hartmann — Mon, 24 Oct 2016 07:48:19 GMT

then try like:

=Concat(distinct Comments, ', ')

hope this helps

Re: Pivot Query

Mon, 24 Oct 2016 07:51:17 GMT

thanks it worked. last thing now is how to sort the comments based on Date column?

thanks in advance

Re: Pivot Query

Frank_Hartmann — Mon, 24 Oct 2016 08:03:01 GMT

without a datedimension i think its not possible to change sortorder within a field in pivottable.

I think then you will have to go with a straight table and hide the date collumn.

hope this helps