US and Worldwide: +1 (866) 660-7555
Results 1 to 7 of 7

Thread: Pivot Table (report) output??

  1. #1

    Question Pivot Table (report) output??

    Hello!

    I have a user request to merge 2 files containing order data, one for a master record and the other one for all the line items for a given order.

    The master file has the following format:
    Code:
    order master  amount 
    1234  $190
    2234 $100
    The detail file has the following format:
    Code:
    order detail  product name  price  qty ordered
    1234    HAMMER         $10    10 
    1234    SCREWDRIVER    $5    10 
    1234    SAW            $20    2 
    2234    DRILL          $100  1
    I am successfully merging (full outer) records from both input links but the output is off:
    Code:
    order master  amount  order detail  product name  price  qty ordered
    1234    $190                            
    2234    $100                            
                          1234    HAMMER         $10    10 
                          1234    SCREWDRIVER    $5    10 
                           1234    SAW            $20    2 
                           2234    DRILL          $100    1
    My user wants the output to look like this instead:
    Code:
    order master  amount  order detail  product name  price  qty ordered
    1234    $190                            
                          1234    HAMMER         $10    10 
                          1234    SCREWDRIVER    $5    10 
                           1234    SAW            $20    2 
    2234    $100                            
                           2234    DRILL          $100    1
    It almost looks like a Pivot table in MS Excel.

    Can this be done? I tried using the row normalizer but to no avail.

    Thanks in advance for your suggestion & help!

    Al.

    PS: Happy Thanksgiving!

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    If you do a full outer join on order the rows should be matched (which you dont' do right now).

    It still does not give you a header and detail kind of thing but just the "master" field repeated for each row that matches.

    Regards,
    Sven

  3. #3

    Default

    Sven-

    I do indeed perform a full outer join on ORDER_MASTER=ORDER_DETAIL in my merge join.

    What I failed to do is add to that step the sequence name from both input steps. Once I realized my error, I was able to produce the output my user requested.

    Thanks for your help.

    Al.

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    BTW ... PDI is not a reporting tool ... if you want fancy headers, ... use e.g. the Pentaho reporting component.

    Regards,
    Sven

  5. #5

    Default

    Hey there, quit mocking me!! Believe me, I am not charge of my users (I wish I was).

    I would love nothing more than using the PDI reporting interface (I played with it...so cool!).

    Until then, I have to be as creative as I can to keep my users happy.

    Thanks for your support.

    Al.

  6. #6

    Default

    PDI Reporting Interface!! Show me this unheard of component I need it now....
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)


  7. #7

    Default

    Quote Originally Posted by bugg_tb View Post
    PDI Reporting Interface!! Show me this unheard of component I need it now....
    I meant (User) Interface...not Programming Interface.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •