Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Last ISO8601 Week data from a CSV file

  1. #1
    Join Date
    Feb 2010
    Posts
    19

    Default Last ISO8601 Week data from a CSV file

    Hello,

    I need to filter a CSV data file after or before loading it.

    The CSV file has a column "fact_date". What I need to do is filter and use only the data where "fact_date" is equal to the last ISO8601 week.

    Example: if today's week (ISO8601) is "20" I'd like to filter and use only "fact_date" equal to "19" week (ISO8601).

    The problem:
    - I don't know how to set a "WHERE" clause to a CSV file based on a var (the last ISO week). Which component from PDI should I use?

    Thanks!

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    You will have to filter after loading it. Use the Filter Rows step. Send the failing rows to a Dummy step and the passing rows onward for further processing.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Feb 2010
    Posts
    19

    Default

    Thanks! But how can I do this dinamicaly?

    Is there any formula to fill in the Filter Rows parameter?
    This Transformation will be executed weekly (schedule) to report the last week and I expect not to edit it every week... Is it possible?

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    Yes, it should be possible to do this dynamically.

    After your CSV Input, use the Get System Info step to get the current date, then use the Calculator step to calculate the ISO8601 week (it's one of the predefined calculations in the Calculator step). At that point you can use the calculated current week number field in the Filter Rows step to get only the rows for the current week.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Nov 2008
    Posts
    777

    Default

    You will also have to calculate the week number of each row in the CSV file in order to do the comparison. This can be done in the same Calculator step.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  6. #6
    Join Date
    Feb 2010
    Posts
    19

    Default

    It's working!

    Thanks!

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.