Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld happening right now! You can still join: 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