# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor II

## Multiplication of rows value

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
Labels (1)
• ### Multiplication

1 Solution

Accepted Solutions

One solution is:

SET DateFormat='DD/MM/YYYY';

tab1:
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;

4 Replies
Partner

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:
resident Raw2;

drop table Raw2;

Date:
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:
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

Contributor II
Author

Thanks Arthur.

Howto do the same through Set Analysis?

Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

One solution is:

SET DateFormat='DD/MM/YYYY';

tab1:
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;