Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: PDI - split a value over several periods

  1. #1
    Join Date
    Aug 2012
    Posts
    12

    Default [Resolved] PDI - split a value over several periods

    Hello,


    I started using PDI for the development of a draft monitoring energy and water consumption.


    I have an excel file type


    Name - start date - end date - value


    example:


    Tom - 01/03/2010 - 20/05/2010 - 10.000 m³




    I would like to save this line in a cube and i need to transform each line to :


    ..
    March 2010 _ 3827 m³
    April 2010 m³ _3.703
    May 2010_2.469 m³
    ..


    I've spent a number of hours on this problem and found no solution ...


    I would of course delighted if someone had a track or an idea to submit to me


    Thank you to the brave that will work on my problem

    ----------------------

    I post here because the reply don't work :



    Waouh !!
    Thank you so much Darrell and Michael !
    It works perfectly well !
    I didn't expect as much.
    I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.


    And now it is perfect !!


    Thank you again
    Last edited by orangeksr; 09-02-2012 at 10:16 AM.

  2. #2

    Default

    Hmm, this is a nasty case.

    What about that think;
    -Calculate the period in a number of days.
    -Divide the consumption by this value - you get now the daily consumption
    -Start a second rowset with a "calendar" on a daily base (generate a row per day), and add a month identifier.
    -Combine the streams (f.e. "Join Rows/Cartesian product", set as filter calendar_date >= period_start and calendar_date <= period_end. You get now a row per day with the daily consumption and calendar information.
    -use the "group by step" to get a sum of the daily consumption per month.

  3. #3
    Join Date
    Nov 2008
    Posts
    777

    Default

    What I would do is this:
    1. Take the date range and calculate how many days that is.
    2. Calculate the usage per day for that date range.
    3. Generate a row in your fact table for each name and for each day in the date range and assign it the daily usage value. Your fact table would look like this:

    Fact Table
    DateKey NameKey Usage

    4. Thus you would reference two dimensions in your fact table: a Date dimension and a Name dimension

    Date Dimension
    DateKey ActualDate DayOfMonth MonthNum YearNum...

    Name Dimension
    NameKey FirstName LastName CompanyName Address...

    5. Create a cube schema for the fact table and the dimensions.
    6. Write an MDX query with the LastName and Values on the columns and YearNum and MonthNum on the rows.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    Attached is a sample that does some of what both Michael and I suggested.

    Preview the Select Values step and that is basically the fact table I was recommending to build. The advantage of this method is that it is very flexible. Once built, it would be easy to generate a report of usage by year instead of by month. You could also get creative in your date dimension and identify summer days versus winter days and analyze that way. In addition, your name dimension could include Province, City, TypeOfCustomer, etc., and provide analysis in those ways as well. The disadvantage of this method is that it takes time to build and maintain the fact table and dimensions in a cube. Volumes have been written on the subject...data warehouse.

    Preview the Sort Rows step and that is the calculation for your statistics as Michael recommended. The advantage of this method is that it is easy to build and run. The disadvantage is that it is not very flexible. You have to change the transformation every time you want a different analysis view.
    Attached Files Attached Files
    Last edited by darrell.nelson; 08-29-2012 at 03:49 PM.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Aug 2012
    Posts
    12

    Default

    Waouh !!
    Thank you so much !
    It works perfectly well !
    I didn't expect much.
    I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative)
    and I had to use the "remove" option in select fied.

    And Now it is perfect !!

    Thank you again Darell and Michael.

  6. #6
    Join Date
    Aug 2012
    Posts
    12

    Default

    Waouh !!
    Thank you so much !
    It works perfectly well !
    I didn't expect as much.
    I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.


    And now it is perfect !!


    Thank you again

  7. #7
    Join Date
    Aug 2012
    Posts
    12

    Default

    Waouh !!
    Thank you so much !
    It works perfectly well !
    I didn't expect as much.
    I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.


    And now it is perfect !!


    Thank you again

  8. #8
    Join Date
    Aug 2012
    Posts
    12

    Default

    Waouh !!
    Thank you so much !
    It works perfectly well !
    I didn't expect as much.
    I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.


    And now it is perfect !!


    Thank you again

  9. #9
    Join Date
    Aug 2012
    Posts
    12

    Default

    Waouh !!
    Thank you so much !
    It works perfectly well !
    I didn't expect as much.
    I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.


    And now it is perfect !!


    Thank you again

  10. #10
    Join Date
    Aug 2012
    Posts
    12

    Default

    Waouh !!
    Thank you so much !
    It works perfectly well !
    I didn't expect as much.
    I don't know why but I had to invert Field A and Field B in the first Calculator (the result was negative) and I had to use the "remove" option in select fied.


    And now it is perfect !!


    Thank you again

  11. #11
    Join Date
    Aug 2012
    Posts
    12

    Default

    Thank You

  12. #12
    Join Date
    Aug 2012
    Posts
    12

    Default Thank you

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.