Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot Query

Hi all,

i have data in the below format:

   

TaskCommentsDate
BBPLC for Swap01 Oct:
- Migration of Non US Population off BCSL
10/20/2016
BBPLC for Swap01 Oct:
- Migration of Non US Population off BCSL
10/19/2016
BCI -Enhancement12 Oct:
-House Position and House ladder protype ready
-Actuals Vs Projections Reconciliation and MTM prototype ready
10/20/2016
BCI -Enhancement12 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:

   

TaskCommentsDate
BBPLC for Swap01 Oct:
- Migration of Non US Population off BCSL
10/20/2016
01 Oct:
- Migration of Non US Population off BCSL
10/19/2016
BCI -Enhancement12 Oct:
-House Position and House ladder protype ready
-Actuals Vs Projections Reconciliation and MTM prototype ready
10/20/2016
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

12 Replies
Not applicable
Author

Any update on this please

MK9885
Master II
Master II

Can you attach the sample data?

Not applicable
Author

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

thanks

Anonymous
Not applicable
Author

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".

MK9885
Master II
Master II

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.

Frank_Hartmann
Master II
Master II

what about this solution?

Unbenannt.png

hope this helps

Anil_Babu_Samineni

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

Capture.PNG

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

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


OR


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


Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vardhancse
Specialist III
Specialist III

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.

Not applicable
Author

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