Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I would much appreciate your help with the following:
I have a table with a Date Column, CustomerID, ProductID and a Column of Production Percentage for each ProductID.
This Percentage is for the End of the Month in each line.
Here is a sample:
Date | CustomerID | ProductID | Production % at End of Month |
---|---|---|---|
31/05/2017 | 4444 | 10 | 70% |
30/06/2017 | 4444 | 10 | 90% |
31/05/2017 | 4444 | 20 | 30% |
30/06/2017 | 4444 | 20 | 60% |
31/05/2017 | 9999 | 10 | 0% |
30/06/2017 | 9999 | 10 | 100% |
31/05/2017 | 9999 | 20 | 0% |
30/06/2017 | 9999 | 20 | 0% |
31/07/2017 | 9999 | 20 | 80% |
So, for example, ProductID 10 for customer 4444 was at 70% at the end of May and at 90% production at the end of June.
That means that During June Production % was 20% (=90% - 70%).
Another example: ProductID 20 for customer 9999 was at 0% at the end of May and also at the end of June. This product was at 80% production at the end of July. Therefore, during July Production % for this product was 80%.
What I would like to ask is what kind of calculation should I use to create this difference of % in production for each product?
Here is how I would like the Difference to show, more or less:
CustomerID | ProductID | June | July |
---|---|---|---|
4444 | 10 | 20% | 0% |
4444 | 20 | 30% | 0% |
9999 | 10 | 100% | 0% |
9999 | 20 | 0% | 80% |
Hope my question is clear.
and thanks in advance for any response.
Sandra
Hi Sandra,
try like this
Temp:
LOAD MonthName(Date) as Month,
CustomerID,
ProductID,
Num([Production % at End of Month]) as %Production
FROM [lib://File]
(html, codepage is 28591, embedded labels, table is @1);
NoConcatenate LOAD *,If(CustomerID=Peek(CustomerID) and ProductID=Peek(ProductID),
RangeSum(%Production,-Peek(%Production)),%Production) as Diff
Resident Temp Order By CustomerID,ProductID,Month;
Drop Table Temp;
Regards,
Antonio
Hi Sandra,
try like this
Temp:
LOAD MonthName(Date) as Month,
CustomerID,
ProductID,
Num([Production % at End of Month]) as %Production
FROM [lib://File]
(html, codepage is 28591, embedded labels, table is @1);
NoConcatenate LOAD *,If(CustomerID=Peek(CustomerID) and ProductID=Peek(ProductID),
RangeSum(%Production,-Peek(%Production)),%Production) as Diff
Resident Temp Order By CustomerID,ProductID,Month;
Drop Table Temp;
Regards,
Antonio
Hi Sandra,
Try the following
x:
Load * Inline
[
Date,CustomerID,ProductID,Production % at End of Month
31/05/2017,4444,10,70%
30/06/2017,4444,10,90%
31/05/2017,4444,20,30%
30/06/2017,4444,20,60%
31/05/2017,9999,10,0%
30/06/2017,9999,10,100%
31/05/2017,9999,20,0%
30/06/2017,9999,20,0%
31/07/2017,9999,20,80%
];
NoConcatenate
data:
Load
RowNo() as Id,
MonthName(Date) as Month,
*,
fabs(if(RowNo()=1 or ProductID<>Previous(ProductID),num([Production % at End of Month],'#0,0'),(1-[Production % at End of Month])-(1-previous([Production % at End of Month])))) as NewPercentage;
Load
Date,
CustomerID,
ProductID,
[Production % at End of Month]
Resident x
order by CustomerID,ProductID,Date;
drop table x;
The above code gives me the picture bellow.
Felipe.
Hi Antonio
Thanks a lot for your reply. It was a great help and ultimately worked well.
I had a problem, which I managed to solve somehow by now -
My Fact table was actually concatenated with other tables. and the noconcatenate clause created a problem.
What I did to solve this was to create another Load and using Resident to the the last table in the script.
Anyway Thanks a lot. Brilliant.
Sandra
Thanks for your reply as well. felipedl
Sandra,
don't worry, You can remove my 'NoConcatenate' prefix.
Also, if You want 0 % value instead of - sign, then replace Expression with
RangeSum(Diff)
Regards,
Antonio