Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to generate & nsert a row for each group of record ?

  1. #1

    Default How to generate & nsert a row for each group of record ?

    Hello all,

    We're getting back orders from fixed length text files posted by customer.
    The same file can content several orders.
    Thanks to Kettle tools it is very simple to map and extract infos from text file and feed the order database.

    Now I would like to generate and insert one and only one row for each orders. This line should have the same structure than the other ones.
    I've tried to watch and understand javascript examples and I'm really puzzled now !

  2. #2

    Default

    Hi Lionel,

    do you get different files or only one big file?
    How do you group your lines/records? (I am asking to find out if it is possible to do the insertion of the extra lines in a separate transformation afterward. In this case you can use SQL to do the grouping.)

    If your grouping allows it, you can do this in one transformation:
    - double all lines (2 outgoing red! hops)
    with the data/stream copied:
    - aggregate the lines using JS step - the lines have to be in correct order! - JS step keeps context (so you can store the aggregated values)
    - reject the lines (filter step)
    - if last line of your group come put all your aggregated values to output (JS step) and do not reject this line
    Let both streams coalescence and write them to db.

    I think it is easier to understand with a separate transformation and some SQL...

    Regards,
    Christoph
    21 is only half the truth

  3. #3

    Default

    Hello Christoph,

    Thank you very much for your kind answer.
    According to your suggestion I'm doing my transformation in 2 steps.
    1. I extract all the lines from the text file with correct data fields distribution. And I integrate all this in my order table.
    2. I extract the order number from the database with a "GROUP BY" query. I use the database lookup step to get back static values from my table and create the new fields (commentary fields which are mainly concatenation and/or tranformation of statics fields).
    The pk problem (a combination of order_number+line_number) has been solved with the sequence generator.

    Now everything works perfectly.

    All the tools were at hand but I was looking for the "key word" to use them... Thank you very much for tell it to me.

    Lionel

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.