• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I have a file with hourly data that needs to be pivot so that we can create time series chart

Ijaz Shay

New Member
Attached is an excel sheet with data for different products and transactions over time. It comes from a report. I need to pivot it so that I can visualize it in a time series. This is a small dataset.
Thanks
Ajay
 

Attachments

  • Test1.xlsx
    257.1 KB · Views: 3
The data structure isn't conducive to PivotTable. Before you can create chart, you need to transform data. You'd need organize it into simple table format.

Also, what do you want to chart? "1-0-110" means nothing to Excel (well just a text). Can you explain a bit more in detail?

Ideally, instead of using reporting function from system/software, see if you can access the database/log file. It's usually more structured (though not as human readable) and easier to load to Data model for PivotTable.
 
The data structure isn't conducive to PivotTable. Before you can create chart, you need to transform data. You'd need organize it into simple table format.

Also, what do you want to chart? "1-0-110" means nothing to Excel (well just a text). Can you explain a bit more in detail?

Ideally, instead of using reporting function from system/software, see if you can access the database/log file. It's usually more structured (though not as human readable) and easier to load to Data model for PivotTable.

Thanks Chihiro:
1-0-110 means 1 transaction 0 errors and 110 processing time in millisec. Basically this file is exported from a crystal report interface but is not formatted correctly. We wish to track errors and transaction over date & time. I was hoping to just have this transformed into a single table structure and then apply pivot and chart transaction volumes and error volumes
You are absolutely correct.. I need to access database. The issue is that we are not allowed to tap into production db (political) and so we have to run reports and massage them. I wish I could transform this into a better format for visualization and analytics (pattern analysis)
Really appreciate your help
Ijaz
 
Try and see if Database people can replicate data in Reporting Server (separate from Production DB).

They can probably set up a job that replicates data in "View" on reporting server. Excluding anything that's not needed in the report. This way, they can still limit access to production db, while giving you access to data to produce reports.
 
I had bit of time so coded data transformation. Click on "Button 1" found in "Sheet1".

It will output to "Result" sheet. On my system, it takes about 17~20 sec to run.

Test it and see if this will meet your need.

Notes:
1. I didn't convert Time from Integer to time value.
2. If the report layout changes, this code will not work.
 

Attachments

  • Test1 (1).xlsb
    216 KB · Views: 0
Back
Top