Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

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

Multiplication of rows value

How to multiply multiple rows value :

 

TYPEITEMStar DateEnd DateVAL
T1IT101/01/201930/01/20192
T1IT201/02/201928/02/20194
T1IT301/03/201930/03/2019-1
T2IT101/01/201930/01/20195
T2IT201/02/201928/02/20194
T2IT301/03/201930/03/20190
T3IT101/01/201930/01/20192
T3IT201/02/201928/02/20194
T3IT301/03/201930/03/2019-5

 

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

TYPEMin StartDateMax Start DateTOTAL VAL
T101/01/201930/03/2019-8
T201/01/201930/03/20190
T301/01/201930/03/2019-40
Labels (1)
1 Solution

Accepted Solutions
Saravanan_Desingh

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;

commQV22.PNG

View solution in original post

4 Replies
Arthur_Fong
Partner
Partner

Try this:

MC.PNG

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
Contributor II
Contributor II
Author

Thanks Arthur.

Howto do the same through Set Analysis?

Brett_Bleess
Employee
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.
Saravanan_Desingh

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;

commQV22.PNG