Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Sum Up field

EvansM
9 - Comet

MBRHISTR1.png

Hello,

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

9 REPLIES 9
BenMoss
ACE Emeritus
ACE Emeritus

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
9 - Comet

 

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

2019-02-13_1240.png

 

 

 

 

 

 

EXPECTED RESULTS TABLE # 2

 

2019-02-13_1244.png

 

 

 

 

 

 

 

 

 

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
20 - Arcturus
20 - Arcturus

@EvansM 

 

How about this:

 

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

[Energy] + [SLCHG]

Cheers,


Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
EvansM
9 - Comet

@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
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
EvansM
9 - Comet

@MarqueeCrew Attached are the data sample and expected reuslts

EvansM
9 - Comet

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

 

Thanks

Evans

MarqueeCrew
20 - Arcturus
20 - Arcturus

@EvansM ,

 

Here's my solve:

 

capture.jpg

 

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
EvansM
9 - Comet

@MarqueeCrew  Thanks a lot

Labels