Multiplication of rows value

[Newbie]

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

[Arthur_Fong]

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

[Newbie]

Thanks Arthur.

Howto do the same through Set Analysis?

[Brett_Bleess]

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/...

Regards,
Brett

[Saravanan_Desingh · Accepted solution]

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;