Sum Up field

[EvansM]

Hello,

I need help to create a work flow with attached scenario.

[BenMoss]

I  think we need to understand exactly what your ingoing data looks like; could you please share an excel file with a sample input; with any compleixities there may be (i.e. for more than one record).

Ben

[EvansM]

Hello @BenMoss ,

Thanks  for your response. I have 14 millions rows of data coming from different inputs. So is kind of hard to put in a single example. All  I want is to break down the changes on [ENERGY] column and [SLCHG] column of TABLE # 1 to look like  

TABLE # 2

EXPECTED RESULTS TABLE # 2

The scenario is: IF [TYPSRV] = "E" THEN the [CHG AMT] is accumulated in [ENERGY]

                           IF [TYPSRV] = "L" THEN the [CHG AMT] is accumulated in [SLCHG].

REFFER TABLE # 2 FOR EXPECTED RESULTS.

Any help I will appreciate.

Thanks

Evans

[MarqueeCrew]

@EvansM 

How about this:

Since they appear to be mutually exclusive, you could just add them.

[Energy] + [SLCHG]

Cheers,

Mark

[EvansM]

@MarqueeCrew  As you see TABLE # 1 [ENERGY]  row #1  the total is 329.77. I want to break down the cost by [TYPSRV] Then add total at the end. Refer table # 2 for expected results.

Thanks

[MarqueeCrew]

@EvansM ,

It would be great to see a set of data (via a text input tool) that has only a focused set of data elements to work from.  Then the expected or desired results.  Looking at the pictures, I'm guessing at the source data.  If you could make this look like a "Weekly Challenge", I'm certain that many folks could contribute help.

Cheers,

Mark

[EvansM]

@MarqueeCrew Attached are the data sample and expected reuslts

[EvansM]

@BenMoss Attached are the data sample and what I am trying to accomplish.

Thanks

Evans

[MarqueeCrew · Accepted solution]

@EvansM ,

Here's my solve:

I took the RAW data and transposed it into a single vertical set of data keyed by Route, Loc, Res, HDate, TypSRV.

I calculated a "Header" with your L or not-L logic.  Then I realized that I had to create a TAX header too.  Then I converted it back horizontally using a Cross Tab tool (aggregating with a SUM).

I think that I match your desired output (different sort order).

Cheers,

Mark

[EvansM]

@MarqueeCrew  Thanks a lot