Accepted Solutions
@tahsin12 try below
Data:
LOAD *,Brand&Segment as SortTmp INLINE [
Category, Cust_no, Year, FiscalMonth, Brand, Segment, Volume
TYR, 23033, 2021, 1, APOLLO, PC, 25
TYR, 23033, 2021, 1, APOLLO, TT, 2
TYR, 23033, 2021, 1, DUNLOP, PC, 44
TYR, 23033, 2021, 2, APOLLO, TB, 12
TYR, 23033, 2021, 3, DUNLOP, PC, 10
];
Input:
noconcatenate
load *,if(peek(SortTmp)=SortTmp and peek(Year)=Year,peek(IncQty)+Volume,Volume) as IncQty;
load * resident Data order by SortTmp,Year,FiscalMonth;
drop table Data;
Final:
load *,1 as Tmpfield resident Input;
Tmp:
load distinct FiscalMonth&'_'&Year as FIELD resident Input;
FOR Each a in FieldValueList('FIELD')
join(Final)
load Brand&Segment as SortTmp,Category, Cust_no, Year, subfield('$(a)','_',1) as FiscalMonth, Brand, Segment, Volume resident Input where FiscalMonth<=subfield('$(a)','_',1) and Year=subfield('$(a)','_',2);
NEXT a
drop table Tmp,Input;
drop fields Tmpfield;
output:
noconcatenate
load Category,SortTmp, Cust_no, Year, FiscalMonth, Brand, Segment, Volume,if(len(IncQty)=0,Volume,IncQty) as IncQty where Flag=0;
load *,if(peek(SortTmp)=SortTmp and peek(FiscalMonth)=FiscalMonth and len(IncQty)=0,1,0) as Flag;
load Category,SortTmp, Cust_no, Year, FiscalMonth, Brand, Segment, Volume,IncQty resident Final order by Year,FiscalMonth,SortTmp,IncQty DESC;
drop table Final
output:
@tahsin12 One Option:
Data:
LOAD *,Brand&Segment as SortTmp INLINE [
Category, Cust_no, Year, FiscalMonth, Brand, Segment, Volume
TYR, 23033, 2021, 1, APOLLO, PC, 25
TYR, 23033, 2021, 1, APOLLO, TT, 2
TYR, 23033, 2021, 1, DUNLOP, PC, 44
TYR, 23033, 2021, 2, APOLLO, TB, 12
TYR, 23033, 2021, 3, DUNLOP, PC, 10
];
Input:
noconcatenate
load *,if(peek(SortTmp)=SortTmp and peek(Year)=Year,peek(IncQty)+Volume,Volume) as IncQty;
load * resident Data order by SortTmp,Year,FiscalMonth;
drop table Data;
Final:
load *,1 as Tmpfield resident Input;
Tmp:
load distinct FiscalMonth&'_'&Year as FIELD resident Input;
FOR Each a in FieldValueList('FIELD')
join(Final)
load Brand&Segment as SortTmp,Category, Cust_no, Year, subfield('$(a)','_',1) as FiscalMonth, Brand, Segment, Volume resident Input where FiscalMonth<=subfield('$(a)','_',1) and Year=subfield('$(a)','_',2);
NEXT a
drop table Tmp,Input;
drop fields Tmpfield,SortTmp;
output:
noconcatenate
load Category, Cust_no, Year, FiscalMonth, Brand, Segment, Volume,if(len(IncQty)=0,Volume,IncQty) as IncQty resident Final;
drop table Final
output:
@tahsin12 try below
Data:
LOAD Category,
Cust_no,
Year,
FiscalMonth as FiscalMonth1,
Brand,
Segment,
Volume
FROM Source;
Join(Data)
LOAD num(FieldValue('FiscalMonth1',RecNo())) as FiscalMonth
AutoGenerate FieldValueCount('FiscalMonth1');
T1:
NoConcatenate
LOAD *
Resident Data
where (FiscalMonth1=1 and FiscalMonth=1) or (FiscalMonth1<=FiscalMonth);
DROP Table Data;
DROP Field FiscalMonth1;
Final:
NoConcatenate
LOAD *,
if(RowNo()=1 or Segment<>Previous(Segment),Volume,
if(FiscalMonth<>Previous(FiscalMonth) and Volume<>Previous(Volume),rangesum(Peek(Volume),Volume),Volume)) as IncQty
Resident T1
Order by Year,Category, Cust_no,Brand,Segment,FiscalMonth,Volume;
DROP Table T1;
Hey @Taoufiq_Zarra thank you for the solution. There is a problem I've faced in your solution. Please take a look on this.
when we adding up previous month data with current data it will only show the current months sumed up data. In this example Dunlop PC IncQty 54 will be shown. The last row where incQty is 44 will not shown in this table. How can I just do this? Please let me know. Thanks in advanced.
Hey @Kushal_Chawda Thank you for your solution. But I've face the same problem in your solution also. Please take a look at this
when we adding up previous month data with current data it will only show the current months sumed up data. In this example Dunlop PC IncQty 54 will be shown. The last row where incQty is 44 will not shown in this table. How can I just do this? Please let me know. Thanks in advanced.
