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: 
erric3210
Creator
Creator

Pivot Table Vertical Rows requirement

Hi Community,

I've a requirement to display data not horizontally but vertically in a Pivot table.

I'm trying from my end but facing some issues. Need your expert help here.

I'm  sharing sample data Excel file. PFA.

Original Data Source table and Pivot Table column dimensions:

Community_1.png

Trying to achieve result with Valuelist Function:

Community_1.1.png

 

Please help.

Regards,

Eric

5 Replies
kleman_emark
Partner - Contributor III
Partner - Contributor III

Hi Eric,

if I understood correctly, you are facing difficulties when getting different profiles for each employee (as there could be multiple combinations for the same employee + state, etc.).

I believe this could be solved in the script using composite key + crosstable (see script bellow - using your input data):

Raw:
LOAD
	Name &'|'& State  &'|'& Salary  &'|'&  Status as %Employee_key,		// composite key for different profiles
	Name,
    Emp_Id,
    State,
    Salary,
    Status,
    Date_Entry
FROM [lib://Desktop/Sample_Data - Community.xlsx]
(ooxml, embedded labels, table is Sheet1)
where Salary <> 0;  // filter out zero values, can be adjusted as necessary



Tmp:
CrossTable(Dimension,Value)
LOAD
	%Employee_key,
    State,
    Salary,
    Status,
    Date_Entry
resident 
	Raw
order by Name;    // for nice ordering of employees in profile numbers


Final:
LOAD
	subfield(%Employee_key,'|',1) as Name,
    autonumber(%Employee_key) as Profile,
    *
resident
	Tmp;
    
drop table Tmp;
drop field %Employee_key;

 

Then in the front-end pivot object you can select:

  • Dimension: Dimension
  • Column: Name, Profile
  • Measures: Value

 

Hope that helps.

Kind regards,

Tomas

 

 

erric3210
Creator
Creator
Author

Hi @kleman_emark,

Thanks for replying to my question. My requirement is very simple.

I just want to transpose Horizontal columns to vertical rows.

The change is required at front end level.

See the desired output:

Community_1.2.png

Regards,

Eric

vinieme12
Champion III
Champion III

Add all your dimensions to Column as normal, just restrict all measures to only evaluate for employees with sum(Salary)>0

example

Measure for Salary

=if(sum(Salary)>0,sum(Salary))       OR                     sum({<Emp_Id={"sum(Salary)>0"}>}Salary)

Measure for Contract

=if(sum(Salary)>0,sum(Contract))     OR                     sum({<Emp_Id={"sum(Salary)>0"}>}Contract)

Measure for SomethingElse

=if(sum(Salary)>0,sum(SomethingElse))    OR                     sum({<Emp_Id={"sum(Salary)>0"}>}SomethingElse)

and so on

 

Then Under Add-Ons>> Data Handling>> Uncheck Include Zero values

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
erric3210
Creator
Creator
Author

Can anyone have solution to this requirement.

vinieme12
Champion III
Champion III

Have you tried this yet!

sum({<Emp_Id={"=sum(Salary)>0"}>}Salary)

sum({<Emp_Id={"=sum(Salary)>0"}>}Contract)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.