Account DescriptionAsset NumberPrior PeriodCurrent Period762400192620635.58 7624001926206 35.58 This is happening with about 100k+ rows. The pattern varies. Some nulls, some rows don't have a current period, others don't have a prior period. Please help.

Can someone help me with this Alteryx issue?

Account Description	Asset Number	Prior Period	Current Period
762400	1926206	35.58
762400	1926206		35.58

This is happening with about 100k+ rows. The pattern varies. Some nulls, some rows don't have a current period, others don't have a prior period. Please help.

Datasets

Accepted answers

apathetichell

this looks like a cross tab... ie data source has:

Account Description	Asset Number	Name	Value
762400	1926206	Prior Period	35.38
762400	1926206	Current Period	35.58

when you cross tab this you get what you have (with some minor regex swapping of characters).

summarize tool group by and max value is probably the easiest way to fix this.If (and only if) it's dynamic and more than a few columns -- -and you need something dynamic you could ---do something more complicated like tile tool - unique value for your two primary keys. cross tab on tile_sequence number as your column ---> value as your value (concat/sum/doesn't really matter what aggregation you use) ---> that should solve problem 1. next you have to fix your column names --- so go back to your tile tool and add a second cross tab ---> filter for tile_number --- not tile tile_sequence_number - 1 -> hook up a dynamic rename between the first datastream and hook the results of the second tile tool into the right dynamic rename anchor. use the change column names based upon right anchor option and map the column name in a to the tile_sequence value -> replace with name.

All comments

alexnajm

What is the end result you are looking for from this table?

RobertHurtFEC

The Asset should only have one line: current period and prior period should be on the same line.

abacon

@RobertHurtFEC Can you post the workflow that generated this? It is hard to say why an output is being given without knowing what is happening.

Bacon

alexnajm

100% agree @abacon 😊

alexnajm

@RobertHurtFEC there are multiple ways to fix this, but best to make sure it fits with the broader context of your workflow

binu_acs

@RobertHurtFEC Try the summarise tool, group by Account Description, Asset Number, then take the maximum value of prior period and current period

apathetichell

this looks like a cross tab... ie data source has:

Account Description	Asset Number	Name	Value
762400	1926206	Prior Period	35.38
762400	1926206	Current Period	35.58

when you cross tab this you get what you have (with some minor regex swapping of characters).

RobertHurtFEC

Thanks everyone! I figured it out and forgot to circle back!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703