source column grouping causing incorrect LAG( ) logic in Multi-Row

Question

Hi Team,

Can you someone please have a look at source data from the attached file and see if we can get expected result using multi-row tool or any other method using Alteryx?

Dur PV_IND_DTL value of PPPV not getting into grouping it becomes zero after multi-row. Is there any solution to this.

Even i try using LAG () SQL function, same column PV_IND_DTL column value causing issue.

This data is at detail level and if this issue resolves, then summation will also be corrected.

Thank You.

Abdul Rasheed.

1060-16-0622.xlsx

apathetichell · Answer

group only by your one grouping column. your problem is probably that one of your pppv resets at one of those columns. If you group by what you need (ie your grouping column). that should fix this.

TE271203 · Answer

Yes, i am using Multi-Row with group by these below columns

PROFABBR
PARTKEY
PRODCODE
IC1
IBC2CODE
REGION_CODE
REGION_PARENT
COMPANY_CODE
ISO_COUNTRY
WWACCT8
TERRITORY_CDE
TERRITORY_ORG_ID
EXCLUSION_CODE
CORPPART
GPL
CBC1CODE
CBC2CODE
COMPETITION_STATUS
PRODUCT_LIFE_CYCLE
PRODUCT_OBJECTIVE
PROFIT CENTER GROUP_ID
LEVEL_5_TERRITORY_CDE
PV_IND_DTL
WEIGHTING

apathetichell · Answer

are you using group by in your multi-row? I'd look into value differences in that field becaues if [Row-1:RV_PRICE_VARIANCE]=null() then 0 else [Row-1:pre_mth_rv_price_variance]+ [RV_PRICE_VARIANCE] endif provides the execpted result in the sample data.

correction - this would give you the sum... if you don't need the sum - you'd just look at [Row-1:RV_PRICE_VARIANCE] ie

if [Row-1:RV_PRICE_VARIANCE]=null() then 0 else [Row-1:RV_PRICE_VARIANCE] endif