topic Re: Multiplication of rows value in QlikView

Multiplication of rows value

Newbie — Sat, 16 Nov 2024 01:47:29 GMT

How to multiply multiple rows value :

TYPE	ITEM	Star Date	End Date	VAL
T1	IT1	01/01/2019	30/01/2019	2
T1	IT2	01/02/2019	28/02/2019	4
T1	IT3	01/03/2019	30/03/2019	-1
T2	IT1	01/01/2019	30/01/2019	5
T2	IT2	01/02/2019	28/02/2019	4
T2	IT3	01/03/2019	30/03/2019	0
T3	IT1	01/01/2019	30/01/2019	2
T3	IT2	01/02/2019	28/02/2019	4
T3	IT3	01/03/2019	30/03/2019	-5

I need output as (e.g Fot T1 : 4*2*-1)

TYPE	Min StartDate	Max Start Date	TOTAL VAL
T1	01/01/2019	30/03/2019	-8
T2	01/01/2019	30/03/2019	0
T3	01/01/2019	30/03/2019	-40

Re: Multiplication of rows value

Arthur_Fong — Wed, 04 Dec 2019 01:52:57 GMT

Try this:

Script:

Raw2:
load TYPE,ITEM,Date#(StarDate,'DD/MM/YYYY')as StarDate,Date#(EndDate,'DD/MM/YYYY')AS EndDate,VAL inline [
TYPE,ITEM,StarDate,EndDate,VAL
T1,IT1,01/01/2019,30/01/2019,2
T1,IT2,01/02/2019,28/02/2019,4
T1,IT3,01/03/2019,30/03/2019,-1
T2,IT1,01/01/2019,30/01/2019,5
T2,IT2,01/02/2019,28/02/2019,4
T2,IT3,01/03/2019,30/03/2019,0
T3,IT1,01/01/2019,30/01/2019,2
T3,IT2,01/02/2019,28/02/2019,4
T3,IT3,01/03/2019,30/03/2019,-5
];

NoConcatenate
Raw:
load TYPE,ITEM,Date#(StarDate,'DD/MM/YYYY')as StarDate,Date#(EndDate,'DD/MM/YYYY')AS EndDate,VAL
resident Raw2;

drop table Raw2;

Date:
load min(StarDate)as minDate,max(StarDate) as maxDate
resident Raw;

let vMin= peek('minDate',0,'Date');
let vMax= peek('maxDate',0,'Date');

Data:
load StarDate, EndDate,VAL,'$(vMin)' as minDate2,'$(vMax)' as maxDate2,
IF(RowNo()=1,VAL,if(PEEK(TYPE)=TYPE,VAL*PEEK(VALMultiple),VAL))AS VALMultiple,
IF(RowNo()=1,1,IF(TYPE=peek(TYPE),1+PEEK(ROWGROUP),1)) AS ROWGROUP,
TYPE,ITEM
resident Raw
order by TYPE ASC,
StarDate ASC;

ROWS:
LOAD MAX(ROWGROUP) AS MAXROWS,TYPE
Resident Data
group by TYPE;
LET vRow= NoOfRows('ROWS');

for i=0 to $(vRow)-1
let vMaxRows = peek('MAXROWS',$(i),'ROWS');

Data2:
load DATE(minDate2,'DD/MM/YYYY')AS minDate,Date(maxDate2,'DD/MM/YYYY') AS maxDate,VALMultiple,TYPE,ITEM,1 as Flag
resident Data
where ROWGROUP= '$(vMaxRows)';
next i
DROP TABLE Data;

drop table Raw;
DROP TABLE Date;

exit Script;

Thanks and regards,

Arthur Fong

Re: Multiplication of rows value

Newbie — Wed, 04 Dec 2019 11:11:13 GMT

Thanks Arthur.

Howto do the same through Set Analysis?

Re: Multiplication of rows value

Brett_Bleess — Wed, 11 Dec 2019 16:10:35 GMT

Couple of useful links on Set Analysis:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Regards,
Brett

Re: Multiplication of rows value

Saravanan_Desingh — Mon, 27 Apr 2020 23:48:50 GMT

One solution is:

SET DateFormat='DD/MM/YYYY'; tab1: LOAD * INLINE [ TYPE, ITEM, Star Date, End Date, VAL T1, IT1, 01/01/2019, 30/01/2019, 2 T1, IT2, 01/02/2019, 28/02/2019, 4 T1, IT3, 01/03/2019, 30/03/2019, -1 T2, IT1, 01/01/2019, 30/01/2019, 5 T2, IT2, 01/02/2019, 28/02/2019, 4 T2, IT3, 01/03/2019, 30/03/2019, 0 T3, IT1, 01/01/2019, 30/01/2019, 2 T3, IT2, 01/02/2019, 28/02/2019, 4 T3, IT3, 01/03/2019, 30/03/2019, -5 ]; tab2: LOAD TYPE, Date(Min([Star Date])) As Min_StartDate, Date(Max([End Date])) As Max_EndDate, Evaluate(Concat(VAL,'*')) As [TOTAL VAL] Resident tab1 Group By TYPE; Drop Table tab1;