Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, expert friends. On this occasion I turn to his powerful knowledge in data transformation. It is possible to obtain the following data transformation.
My initial data:
base_temp:
load * inline [
Periodo, Customer, Venta
201701,A,100
201702,B,200
201703,C,300
201704,A,400
201705,B,500
201706,C,600
201707,A,700
201708,B,800
201709,C,900
201710,A,1000
201711,B,1100
201712,C,1200
201801,A,1000
201802,B,2000
201803,C,3000
201804,A,4000
201805,B,5000
201806,C,6000
201807,A,7000
201808,B,8000
201809,C,9000
201810,A,10000
201811,B,11000
201812,C,12000
];
The transformed data that I want to obtain:
- Rango: Calculated field. If 201701 => 201701 - 201701; if 201703 => 201701 to 201703. .....
- Total Venta: accumulated sum accumulated sum according to the rank (rango)
Periodo | Customer | Rango | Total Venta |
---|---|---|---|
201701 | A | 201701-201701 | 100 |
201702 | A | 201701-201702 | 100 |
201703 | A | 201701-201703 | 100 |
201704 | A | 201701-201704 | 500 |
201705 | A | 201701-201705 | 500 |
201706 | A | 201701-201706 | 500 |
201707 | A | 201701-201707 | 1200 |
201708 | A | 201701-201708 | 1200 |
201709 | A | 201701-201709 | 1200 |
201710 | A | 201701-201710 | 2200 |
201711 | A | 201701-201711 | 2200 |
201712 | A | 201701-201712 | 2200 |
201801 | A | 201801-201801 | 1000 |
201802 | A | 201801-201802 | 1000 |
201803 | A | 201801-201803 | 1000 |
201804 | A | 201801-201804 | 5000 |
201805 | A | 201801-201805 | 5000 |
201806 | A | 201801-201806 | 5000 |
201807 | A | 201801-201807 | 12000 |
201808 | A | 201801-201808 | 12000 |
201809 | A | 201801-201809 | 12000 |
201810 | A | 201801-201810 | 22000 |
201811 | A | 201801-201811 | 22000 |
201812 | A | 201801-201812 | 22000 |
I hope you can help me. thank you
You can do this using an AsOf table:
Excellent reference, my friend, I had to make some changes but it helped me a lot.