how to get Max(DATE) using transformer

[pxraja]

Hi all,
hash
I am using ODBC-->TRF---> ODBC

i want to take maxium date for lookup

how can i get in transformer for every records i want only the max dated recor

inlink.field01 inlink.field02
jkdkal 2007/12/12
jkdkal 2008/01/01

I want to take record to ouput column

outlink.field01 outlink.field01
jkdkal 2008/01/01

[ray.wurlod]

Sort by field01 then by field02 (descending) prior to the Transformer stage and use stage variables to detect a change in field01. Constrain the output to those rows where you detect a change in field01.

[pxraja]

Hi Ray,

Thanks for your suggestion,

will RowProcCompareWithPreviousValue work for DATE column
in oracle database.

suggestions are welcome

Thanks in Advance

[ray.wurlod]

This is a DataStage function so it will only work within DataStage (in no sense will it work "in Oracle database"). It still relies on sorted data to work properly.

[pxraja]

Hi all,

I want to select the first row as a updated one and reject the other rows, for example

inlink.field01 inlink.field02
jkdkal 2007/12/12
jkdkal 2008/01/01
jkdkal 2006/01/01
fkdajj 2005/05/04
fkdajj 2008/04/08
fkdajj 2008/08/23


I want to take record to ouput column as follows

outlink.field01 outlink.field01
jkdkal 2008/01/01
fkdajj 2008/08/23

I can use sort stage for rearranging the columns, as ray quoted " first by field01 and field02(descending).

how to use stage variable to allow only the first row

any ideas or workarround are welcomed

Thanks in advance

[syeed]

Hi,

Map the value like this

outlink.field01 ==> StageVar1
if StageVar1=StageVar2 then 'R' else 'P' StageChkVar
outlink.field01 ==> StageVar2

In Transformer Constraints, u can have condition as StageChkVar='P'

Other Simple Way would be have a remove duplicate stage Sort data based on first by field01 and field02(descending) and Remove duplicate key as field01 with Retain=First Record.

Hope this will help you.

Thanks.

[chulett]

There's no 'Remove Duplicates' stage in Server. Well, there is but it's called the Transformer.

[ray.wurlod]

No-one on this thread claimed that there was a Remove Duplicates stage in server jobs.

[chulett]

Server forum, Server post, PX advise. Hence the comment.

Other Simple Way would be have a remove duplicate stage Sort data based on first by field01 and field02(descending) and Remove duplicate key as field01 with Retain=First Record.

[battaliou]

You can aggregate your sorted data on the key and choose "last" option for your date. This will function the same as a remove duplicate stage.

[pxraja]

Hi all,

Thanks for your suggestion,

but I am using Inlink.field0, Inlink.field1 as varchar in oracle database and Outlink.field1 as number and Outlink.field2 as Timestamp

while i am using Iconv(Inlink.field1,'D-YMD[4,2,2]') or Oconv(Iconv(Inlink.field1,'D-YMD[4,2,2]'),'DQ4')

the Outlink.field1 is empty or NULL While it is pass without any of Iconv or Oconv it's working fine. I donot get why this response is occuring.
The job runs successfully without any warnings

Please clarify this professionals

[ArndW]

Since your date is in the form YYYY/MM/DD there is no need to convert to internal format. The nulls or empties are a separate issue.

[pxraja]

Hi all,

Can any one tell me why it's showing null field, whenever I do conversion on that particular field.

i.e., on Inlink.field01 as mentioned earlier.

I could not able to get quarters of that field.

[pxraja]

Hi all,

Can any one tell me why it's showing null field, whenever I do conversion on that particular field.

i.e., on Inlink.field01 as mentioned earlier.

I could not able to get quarters of that field.

[ArndW]

Outlink will only be NULL if the input is NULL