Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data:
I
ID | LINE | DATE | Collis |
---|---|---|---|
131 | 501 | 2018-09-25 | 67 |
145 | 501 | 2018-09-25 | 40 |
123 | 702 | 2018-09-25 | 20 |
99 | 702 | 2018-09-25 | 50 |
87 | 105 | 2018-09-25 | 73 |
Ok, i know that the sum is 107 of collis per Line.
Now i want to show in a new column the procent of each Line. after collis
Like this:
67/107 Givs 62,62%
40/107 givs 37,38%
and so on.
The last post gives me 100% it's only one
The collis are count(ColliNbr)
I have tried aggr ,but i can't get it right.
Simple enough, mate...the script is similar. Just change the initial data table with your sql;
data:
Select
ID,
LINE,
DATE,
Collis
From your_as400_database;
Then add the following;
Left Join(data)
load
LINE,
DATE,
Sum(Collis) as Collis_Sum
Resident data
Group By
LINE,
DATE;
final:
NoConcatenate
load
ID,
LINE,
DATE,
Collis,
Collis / Collis_Sum as Percent
Resident data;
drop table data;
assuming collis expression is sum(value)
you can add a new measure sum( TOTAL value)
see below
Obviously, there is much more data than this to consider.
But, it seems you are desiring to sum Collis grouping by LINE and/or DATE yet not ID to get your denominator, then divide the Collis into the sum. Is that correct? Please clarify.
V/r,
John
Sorry it must be by LINE and Date. I have updated the post above
But i have to count total grouping by LINE
If i add Total it gives me 250 on each row.
Try this...
data:
load * inline [
ID, LINE, DATE, Collis
131, 501, 2018-09-25, 67
145, 501, 2018-09-25, 40
123, 702, 2018-09-25, 20
99, 702, 2018-09-25, 50
87, 105, 2018-09-25, 73
];
Left Join(data)
load
LINE,
DATE,
Sum(Collis) as Collis_Sum
Resident data
Group By
LINE,
DATE;
final:
NoConcatenate
load
ID,
LINE,
DATE,
Collis,
Collis / Collis_Sum as Percent
Resident data;
drop table data;
Missed that part.
whats the aggr function you are using? and the output
I like it, but the data is coming from AS400 and the script is SQL based. I cannot use inline in this part. Sorry mate,
Simple enough, mate...the script is similar. Just change the initial data table with your sql;
data:
Select
ID,
LINE,
DATE,
Collis
From your_as400_database;
Then add the following;
Left Join(data)
load
LINE,
DATE,
Sum(Collis) as Collis_Sum
Resident data
Group By
LINE,
DATE;
final:
NoConcatenate
load
ID,
LINE,
DATE,
Collis,
Collis / Collis_Sum as Percent
Resident data;
drop table data;
Thats will doit!!! worked 100% Thanx mate!!