Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Generate multiple files from 1 query

  1. #1

    Question Generate multiple files from 1 query

    Hello all,

    I have tried several ways and tutorials and i cant figure this one out. This is what i want to do:

    I have a Table input with a query which lists all debtors. This result is sent to an XML file which is converted to a HTML using XSL. The HTML file is emailed to the ones required.
    This all works fine, but now the managers want more. They want to click on the debtors name in the email and then see all the invoices which are not paid jet.
    I created the hyperlinks in the email, and tested it and this also works fine. But now i have a problem.
    This hyperlinks are linking to a seperate HTML file which has to be created for each debtor.
    We have over 3000 debtors so i dont want to create a seperate HTML file for each one of them every week.
    Is this possible with kettle?
    So i have a query like:

    Code:
    SELECT DebtorNr, Name, InvoiceNr, InvoiceAmount, InvoiceDate, OutputFilename
    FROM OpenItems
    WHERE InvoiceAmount > 0
    AND InvoiceDate < current_date()

    Now i want for each DebtorNr a seperate HTML file which name is defined in the column OutputFilename. (I embedded these filenames in my hyperlinks)

    Is this possible?

  2. #2

    Default

    Hello,

    Can you send me the sample ktr you have written so that I can look into this ?

    Regards

    Nitesh

  3. #3
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    I would use a "loop" for each email that you create.
    You can find some examples if you search for "PDI loop multiple file" with Google.

    But the principle is:
    1. create a list - in your example debtors
    2. send that list to a transformation or a job, using the option "execute for each row"
    3. the transformation or job will apply all your data processing for each debtor one at the time

    Note that looping can cause some memory leaking and if you have many loops at some point you'll be out of memory.
    Last edited by Mick_data; 12-08-2015 at 07:41 AM. Reason: grammar!
    -- Mick --

  4. #4

    Default

    Hello mick!

    Thanks for your advice but i didnt get it to work. What i have now are two transformations:
    The first one only has one table input with the following SQL
    Code:
    SELECT DISTINCT DebtorNr
    FROM OpenItems
    WHERE invoiceamount > 0
    AND InvoiceDate > current_date()
    This gives me a list with 2689 debtors.

    For my next transformation i checked execute once for every input row. (So this one should execute 2689 times)
    But now i have the following code.

    Code:
    SELECT DebtorNr, Name, InvoiceNr, InvoiceAmount, InvoiceDate, OutputFilenameFROM OpenItems
    WHERE InvoiceAmount > 0
    AND InvoiceDate < current_date()
    AND DebtorNr = ?
    And next an XML output which has to be created with the OutputFilename.
    The transformation doesnt give any errors, but no xml is generated.
    I should have 2689 XML files

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Just a small suggestion, but use the right tools for the job.
    I would use BAServer and PRD to do this.

    BAServer gives you the whole realm of reporting tools to work with. You can convert your existing report to a report that is available on the BAServer for the managers to look at. This report can then have a drill-down report showing the invoices.

    If you *MUST* still have the report emailed out, with hyperlinks to the invoices outstanding, then you can embed the link to the BAServer's drill-down report included in the emailed report. It would end up looking something along the lines of http://baserver.yourorg.internal/pen...debtornr=12345

  6. #6

    Default

    Hello Joey,

    I think to loop you second transformation you will need to check "execute for every input row ?" in properties and then in first transformation copy rows to result and in second get rows from result. but for a proper loop you will need to create a sub job with second transformation.

    If you want I can show you how you can do this ?...with your transformations.

    Regards

    Nitesh

  7. #7

    Default

    Hello Nitesh,

    Thank you for your reaction. It works now!
    I never used another job within a transformation but it works like a charm!
    Now i create the xml files, generate the HTML files with a XSL transformation, copy the HTML files to my webserver and finally send the email with the hyperlinks to this webserver.

    Thank you all!

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.