1

I'm using pentaho kettle 5.2.0 version. I'm trying to do pivots on my source data,here it is the structure of my source

Billingid   sku_id    qty
  1           0        1
  1           0        12
  1           0        6
  1           0        1
  1           0        2
  1           57       2
  1          1430      1
  1          2730      1
  2          3883      2
  2          1456      1
  2          571       9
  2          9801      5
  2          1010      1

And this is what I'm Expecting

billingid   0   57  1430  2730  3883  1456  571  9801  1010
   1                *******sum of qty******
   2

this is the steps which I did in row denormaliser, but unable to get expected output

Any help would be much appreciated ..THANKS in advance

Deepesh
  • 820
  • 1
  • 14
  • 32
  • Are you just trying to count qty pr. billingid? – bolav Feb 27 '16 at 09:00
  • No @bolav , I 'm trying sum the qty against billingid vs sku_id. I want the sum of qty against column billingid and sku_id – Deepesh Feb 27 '16 at 09:40
  • Can you do with a format where you get a row where the billingid and sku_id combination is unique, and you get billingid, sku_id, sum_qty, or do you have to denormalize? (The first alternative is much easier to make) – bolav Feb 27 '16 at 09:55
  • Although your first suggestion is easier but still if I want to go with denormaliser then how do I proceed with? What could be the necessary changes do I need to make in the screenshot of the denormaliser step? – Deepesh Feb 27 '16 at 10:07

1 Answers1

2

For denormaliser to work, you first have to Sort, and the Group the rows, to have the sum of qty. So the transformation is going to look something like this:

screenshot of transformation

Sort on billingid and sku_id, and then sum the qty, group by billingid and sku_id:

screenshot of group by

Then in denomaliser you need to list each different sku_id, to make a column pr id.:

screenshot of denomaliser

This will give the expected output:

screenshot of expected output

bolav
  • 6,938
  • 2
  • 18
  • 42
  • that's awsome!!! it really made my day :) Thanks alot . the only thing I'm worried about now is if the number of record increase in the "sku_id" column then it would be very tedious task to manually write all values in Key value column of denormaliser step. any suggestion on this? – Deepesh Feb 27 '16 at 12:17
  • 1
    @Deepesh: You can use [ETL Metadata Injection](http://wiki.pentaho.com/display/EAI/ETL+Metadata+Injection). – bolav Feb 27 '16 at 12:18
  • I'll surely try that step. Once again thanks :) @bolav – Deepesh Feb 27 '16 at 12:27