Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: hierarchical file output

  1. #1

    Default hierarchical file output

    Please, I have hierarchy of data in 3 tables: invoice, invoice_ item and item _tax.
    I have to build an output file where the records sequence obeys this hierarchy:
    Invoice A
    Item A1
    Tax A1.1
    Tax A1.2
    Item A2
    Tax A2.1
    Tax A2.2
    Invoice B
    Item B1
    Tax B1.1
    Tax B1.2
    Invoice C
    Item C1
    Tax C1.1
    Item C2
    Tax C2.1
    Item C3
    Tax C3.1

    I can do this using row sort but it won’t work when I have something like 10.000 invoices.

    I can read in the proper order. For each record in the invoice table I select the related items, and for each item I select the related tax. But when writing to the output file this order is lost. I don’t know how to hold on the input of the next invoice until the last tax of the last item have been written to the output file.
    Can any one help?
    Thanks

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

    Default

    At first sight 1 big tricky SQL query. For the moment I don't see another solution.

    Try to get something for output like (using a part of your example):

    A 0 0 Invoice A
    A 1 0 Item A1
    A 1 1 Tax A1.1
    A 1 2 Tax A1.2
    A 2 0 Item A2
    A 2 1 Tax A2.1
    A 2 2 Tax A2.2
    B 0 0 Invoice B
    B 1 0 Item B1
    B 1 1 Tax B1.1
    B 1 2 Tax B1.2

    which would be the result of a union of 3 queries (sorted on the first 3 columns)

    A 0 0 Invoice A
    B 0 0 Invoice B

    A 1 0 Item A1
    A 2 0 Item A2
    B 1 0 Item B1

    A 1 1 Tax A1.1
    A 1 2 Tax A1.2
    A 2 1 Tax A2.1
    A 2 2 Tax A2.2
    B 1 1 Tax B1.1
    B 1 2 Tax B1.2

    Regards,
    Sven

  3. #3

    Default

    Yes I can order the union of querying the related information in the 3 tables but this means to take the sort from Kettle to the database.

    The hierarchical output it’s a very simple thing that any reporting tool with master-detail loop can do. There should be a way to do this with Kettle.

    I thought of splitting in 3 transformations: one to query each table. Then for each line of the result set from previous transformation I execute the next one. The only problem is that it will have to wait writing 10.000 invoices in the result set before querying the related items and tax.

    Any way, thank you for the quick reply.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The hierarchical output it’s a very simple thing that any reporting tool with master-detail loop can do. There should be a way to do this with Kettle.
    Repeat after me: "Kettle is NOT a reporting tool!"

    We will be building a plugin for Pentaho Reporting later this year, but actually I like the differentiation A LOT.

  5. #5

    Default

    OK Chief Data, only I not doing reporting but ETL and it is hard to understand why something powerful like Kettle can’t do this easy hierarchical output. Maybe some kind of synchronization between steps would work nice.

  6. #6

    Thumbs up

    I just found that the “sorted merge” does what I need. Only had to order the query on each table. No overload on the database and no need to wait help from reporting tool. Thanks for every body who tried to help.

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.