Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
my aggregation is not working. The table is
_Date, ID, Event
4.2.2015, 71, O
4.2.2015, 6, O
4.2.2015, 31, O
1.1.2015, 3, I
3.1.2015, 5, I
3.1.2015, 61, I
3.1.2015, 7, I
and the expression
aggr(RangeSum(Above( count({$< Event = {I}>} ID) - count({$< Event = {O}>} ID), 0, rowno())), Land, Month_Year)!
The wrong Result in the table you can see in the attacted file. The correct result would by
Jan 2015 D = 2
UK = 1
A = 1
Feb 2015 D = 1
A = 0
UK = 0
I think the problem is the sorting of the values in the INLINE table?! But how I can handle this problem?
Thank you
having personal edition so cannot open qvw but I think Event values in set expression needs to be in single quote, It appears 'I' and 'O' are text values so may need single quote. Not sure if more things involved.
OK update...
In the attachted fiel you can see the table with the expressions IN and OUT. About this 2 expressions you want to make a accumulation. The Goal is to have a table or a diagramm with dimension Month_Year and a accumulation about the several lands... Rigth beside the table you can see the right result !
Next Script:
Fact:
LOAD * INLINE [
_Date, ID1, Event
4.2.2015, 71, O
4.2.2015, 6, O
4.2.2015, 31, O
1.1.2015, 3, I
3.1.2015, 5, I
3.1.2015, 61, I
3.1.2015, 7, I
];
Land:
LOAD * INLINE [
ID1, Land
3, D
31, D
5, D
6, A
61, A
7, UK
71, UK
];
Join(Fact)
LOAD *
Resident Land;
FinalFact:
LOAD _Date,
ID1 as ID,
Event
Resident Fact
Order By Land, _Date;
FinalLand:
LOAD ID1 as ID,
Land
Resident Land;
DROP Tables Fact, Land;
Tmp_Fact:
NoConcatenate
LOAD *
Resident FinalFact;
Calender:
NoConcatenate
LOAD _Date
, _Date as Date
, month(_Date) as Month
, year(_Date) as Jahr
, date(MonthStart(_Date), 'MMM-YYYY') as Month_Year
Resident FinalFact;
New Expression:
Aggr(RangeSum(Above(Count({$<Event = {I}>} ID), 0, RowNo()), -Above(Count({$<Event = {O}>} ID), 0, RowNo())), Land, Month_Year)
I believe you just need to order your Tmp_Fact table, create your calendar from that and drop the Tmp_Fact table:
Fact:
LOAD * INLINE [
_Date, ID, Event
4.2.2015, 71, O
4.2.2015, 6, O
4.2.2015, 31, O
1.1.2015, 3, I
3.1.2015, 5, I
3.1.2015, 61, I
3.1.2015, 7, I
];
Tmp_Fact:
NoConcatenate
LOAd
*
Resident Fact
Order by ID, _Date;
Calender:
NoConcatenate
LOAD
_Date
, _Date as Date
, month(_Date) as Month
, year(_Date) as Jahr
, date(MonthStart(_Date), 'MMM-YYYY') as Month_Year
Resident Tmp_Fact;
DROP TABLE Tmp_Fact;
Land:
LOAD * INLINE [
ID, Land
3, D
31, D
5, D
6, A
61, A
7, UK
71, UK
];
Aggr() will sort its dimension values by load order, so you need to sort your calendar fields chronological when loading in, to make above() work as expected in the aggr() expression.
Stefan -
I think we might have got lucky that ID and Land will be sorted same way using the current data, but what if this was the Land Table?
Land:
LOAD * INLINE [
ID, Land
3, D
31, A
5, D
6, D
61, A
7, UK
71, UK
];
I don't think that Land would sort correctly in that case.
Land sorted correctly with respect to what? I believe it doesn't matter for the aggr() above() expression, since it will be reset at column segment borders anyway.