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

Pivot Table or Power PT

Camilo Amarcy

Camilo Amarcy

New Member
Hi All,

I have an assignment with the following rules:

Phase 1:

File Specification
1) 250k rows (each row is related to a specific customer)
2) For each customer I have Debit Value, Credit Value and Balance Value.

The file has a monthly information for all customers and it looks like:
Coll A= Customer ID
Coll B = Debit Value (Day 1)
Coll C = Credit Value (Day 1)
Coll D = Balance Value (Day1)

Coll E= Customer ID (note: position of customer ID is not the same of Coll A, because customers perform debit or credit in different hours). Ex: on Coll A the customer 0001 is on row 3, and on day 2 the customer can be on row 200000.
Coll F = Debit Value (Day 2)
Coll G = Credit Value (Day 2)
Coll H = Balance Value (Day2)

Same happens for Day 3 till day 30.

I used VLOOKUP and according with my maths it was required 7,5 millions of VLOOKUPS (it took 8 hours to process Credit Value for 30 days).

Phase 2:

After the tables become organized, I need to extract the maximum Credit Value for each customer during the month. In order to discovery what is his/her salary and assign a tag (according with our quallification)

Any Suggestions to optimize this query with Pivot Table or Power Pivot?
 
Hi Camilo

Where does the data come from? Some other business system?

I can't help but feel that the data is set-up badly. I wouldn't be repeating 4 columns for each day. Instead I'd have 5 columns, adding a column to represent the day/date. Then certainly worth crunching in PowerPivot.
 
Hi Jon,

I am not sure about the source of the file. I just received this file with this misc information.

The fist exercise basically is to organize the file. (that is the hard part).
 
I would go back to the originator and ask for it in a workable structure. Given the structure you currently have I think it's perfectly reasonable to demand it in a better structure. There is no way the data is organised like that in the DB.
 
You must log in or register to reply here.
Back
Top