Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
data:
LOAD * Inline [
Group, Subgroup, Date, Value, Value2
Group1, Subgroup1, 01/01/2017, 100, 2
Group1, Subgroup1, 01/02/2017, 0, 3
Group1, Subgroup1, 01/03/2017, 200, 2
Group1, Subgroup1, 01/04/2017, 0, 1
Group2, Subgroup1, 01/05/2017, 100, 4
Group2, Subgroup1, 01/06/2017, 0, 2
Group2, Subgroup1, 01/07/2017, 0, 1
]
I need to get the result as in table2. But I would like to show some logic in table1.
Any suggestion is welcome.
table1
Group | Subgroup | Date | Value | RepeatValue | Value2 | Multiply( Sum(Value2)*RepeatValue ) |
---|---|---|---|---|---|---|
Group1 | Subgroup1 | 01/01/2017 | 100 | 100 | 2 | 200 |
Group1 | Subgroup1 | 01/02/2017 | 0 | 100 | 3 | 300 |
Group1 | Subgroup1 | 01/0/3/2017 | 200 | 200 | 2 | 400 |
Group1 | Subgroup1 | 01/04/2017 | 0 | 200 | 1 | 200 |
Group2 | Subgroup1 | 01/05/2017 | 100 | 100 | 4 | 400 |
Group2 | Subgroup1 | 01/06/2017 | 0 | 100 | 2 | 200 |
Group2 | Subgroup1 | 01/07/2017 | 0 | 100 | 1 | 100 |
table2
Group | Sum(Multiply) |
---|---|
Group1 | 1100 |
Group2 | 700 |
Create the RepeatValue field in the script:
SET DateFormat = 'MM/DD/YYYY';
INPUT:
LOAD * Inline [
Group, Subgroup, Date, Value, Value2
Group1, Subgroup1, 01/01/2017, 100, 2
Group1, Subgroup1, 01/02/2017, 0, 3
Group1, Subgroup1, 01/03/2017, 200, 2
Group1, Subgroup1, 01/04/2017, 0, 1
Group2, Subgroup1, 01/05/2017, 100, 4
Group2, Subgroup1, 01/06/2017, 0, 2
Group2, Subgroup1, 01/07/2017, 0, 1
];
RESULT:
LOAD *,
If(Value = 0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue),Value) as RepeatValue
Resident INPUT
ORDER BY Group, Subgroup, Date asc;
DROP TABLE INPUT;
Then use the field in your chart expression
Group | Sum(RepeatValue*Value2) |
---|---|
1800 | |
Group1 | 1100 |
Group2 | 700 |
Create the RepeatValue field in the script:
SET DateFormat = 'MM/DD/YYYY';
INPUT:
LOAD * Inline [
Group, Subgroup, Date, Value, Value2
Group1, Subgroup1, 01/01/2017, 100, 2
Group1, Subgroup1, 01/02/2017, 0, 3
Group1, Subgroup1, 01/03/2017, 200, 2
Group1, Subgroup1, 01/04/2017, 0, 1
Group2, Subgroup1, 01/05/2017, 100, 4
Group2, Subgroup1, 01/06/2017, 0, 2
Group2, Subgroup1, 01/07/2017, 0, 1
];
RESULT:
LOAD *,
If(Value = 0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue),Value) as RepeatValue
Resident INPUT
ORDER BY Group, Subgroup, Date asc;
DROP TABLE INPUT;
Then use the field in your chart expression
Group | Sum(RepeatValue*Value2) |
---|---|
1800 | |
Group1 | 1100 |
Group2 | 700 |
Stefan, thanks for solution.
Would you please advise how to make the same in case where two different tables?
Script below doesn't provide an expected result.
INPUT1:
LOAD * Inline [
Group, Subgroup, Date, Value
Group1, Subgroup1, 01/01/2017, 2
Group1, Subgroup1, 01/02/2017, 3
Group1, Subgroup1, 01/03/2017, 2
Group1, Subgroup1, 01/04/2017, 1
Group2, Subgroup1, 01/05/2017, 4
Group2, Subgroup1, 01/06/2017, 2
Group2, Subgroup1, 01/07/2017, 1
];
Left Join(INPUT1)
INPUT2:
LOAD * Inline [
Group, Subgroup, Date, Value2
Group1, Subgroup1, 01/01/2017, 100
Group1, Subgroup1, 01/03/2017, 200
Group2, Subgroup1, 01/05/2017, 100
];
RESULT:
LOAD *,
If(Value2 = 0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue2),Value2) as RepeatValue2
Resident INPUT1
ORDER BY Group, Subgroup, Date asc;
DROP TABLE INPUT1;
When checking if the value of Value2 is a valid one, you need to consider that due to the JOIN, you need to check for NULL, not zero.
RESULT:
LOAD *,
If(Len(trim(Value2))=0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue2),Value2) as RepeatValue2
Resident INPUT1
ORDER BY Group, Subgroup, Date asc;
It works. Thank you very much!