Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey there,
I'm new to qlikview. I need help regarding a requirements. I'll give the data I have working on and then I'll explain what are the requirements are.
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 |
I have to calculate the incentive. The table will be like this
Category | CustNo | Year | FiscalMonth | Brand | Seg | Volume | IncQty |
TYR | 23033 | 2021 | 1 | APOLLO | PC | 25 | 25 |
TYR | 23033 | 2021 | 1 | APOLLO | TT | 2 | 2 |
TYR | 23033 | 2021 | 1 | DUNLOP | PC | 44 | 44 |
TYR | 23033 | 2021 | 2 | APOLLO | PC | 25 | 25 |
TYR | 23033 | 2021 | 2 | APOLLO | TT | 2 | 2 |
TYR | 23033 | 2021 | 2 | DUNLOP | PC | 44 | 44 |
TYR | 23033 | 2021 | 2 | APOLLO | TB | 12 | 12 |
TYR | 23033 | 2021 | 3 | APOLLO | PC | 25 | 25 |
TYR | 23033 | 2021 | 3 | APOLLO | TT | 2 | 2 |
TYR | 23033 | 2021 | 3 | DUNLOP | PC | 44 | 44 |
TYR | 23033 | 2021 | 3 | APOLLO | TB | 12 | 12 |
TYR | 23033 | 2021 | 3 | DUNLOP | PC | 10 | 54 |
The previous month volume will be added to the current month incentive calculation in qlikview script. Total calculation will be in qlikview script. I tried but couldn't find any solution. Please help me to solve this problem. Thanks.
Regards
tahsin12
@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:
Thank you so much @Taoufiq_Zarra for the solution.😊
@tahsin12 although you got the solution, I would recommend not to use for loops as it will affect the performance of the app. So I would suggest to use 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:
LOAD *
where Flag=1;
LOAD *,
if(rowno()=1 or FiscalMonth<>Previous(FiscalMonth),1,0) as Flag,
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;
Thank you @Kushal_Chawda I will keep this in my mind definitely. Thanks for all the help 😊