Skip to Content
author's profile photo Former Member
Former Member

max() function in Data services

Hi Experts,

i'm using Data services (etl), and working on budget table(ms sql table),

1. the table has a field [budget amount] and [version_no],

2. before i extract the budget amount i need to know the highest version in [version_no] fields

3. can any body help me on this? i tried to use the script: ifthenelse(max(version-no),budget_amount, 0) but it retuns error when validation.

please help me on this:

thanks in advance

Archie72

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Feb 02, 2012 at 10:06 AM

    Hi,

    Connect a Query transform say Query1 from the source table

    Inside Query1

    select MAX(version no) into the output. Do not select the other budget field into the output.

    Connect another Query transform say Query2 from the source table. Connect the output of Query1 also into Query2.

    Inside Query2:

    Join the source table and Query 1 in where

    Query1.version no = SourceTable.version no

    Select version no and budget into Query 2 output and connect it to target table.

    Assuming that the source table has only version number and budget. You might have to tweak and make use of Group By as required where you use the MAX() function. SourceTable mentioned in the where condition will be replaced with your exact source table name

    Regards,

    Suneer

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 02, 2012 at 10:07 AM

    max() is an aggregate function which can be used only when you do a "group by" operation.

    If you want to pick the budget amount corresponding to the maximum version number, you can use the max function like this:

    in the query transform mapping put max(budget_amount) and in the group by tab, put the version number column.

    Does your budget table has only two columns? If version_no and budget_amount are two among the other columns, then i think you may have to get the max(version) agianst other columns (perhaps year, object , etc) and then join this version with the original dataset. This will give you records belonging to the highest version.

    Regards,

    Shine

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.