I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

[channa43]

I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

In Di Studio (Version 4.901) I try this in an Extract node and gives an error "Summary Function restricted to Select clause" etc.

The expression is;   catx("_",(min(intnx('Month',(Datepart(Policy_Period_Create_Time)),0,'E')) ),Account_Number)

 However  the same expression runs correctly in SAS EG.

Appreciate any  help here.

Cheers

[PGStats]

The error message isn't about the expression itself. It is about where in the query you used it. Give more détails, please.

[channa43]

Hi PG

I am creating DIS reporting job, in the process I have to select the minimum effective date (month)  group by account ids  (and creating a unique Id with the effective month and  Account ID).

So I created this in SAS EG and all worked fine, however when I re created this step in DIS the error was given and couldn't go beyond.

Attached a screenshots of  the Extract Mapping hope this will help.

Thanks heaps

Channa

[LinusH]

I need to see the full mapping tab, and the full generated code for that step.

[LinusH]

Please show the generated code, and potentially screen shots on relevant transformation tabs.

[channa43]

Hi

The DIS Code is given below , I am creating an ID with  minimum date and Account id, so unable to execute the minimum date  expression in DIS in a extract transformation, however the code  works in SAS EG.

Thanks heaps

Channa

proc sql;

   create table work.Subsequent_Quotes_0 as

      select

         Account_ID,

       (intnx('Month',(Datepart(_Period_Create_Time)),0,'E')) as

         Period_Created_Month length = 8

            format = DATETIME22.3

            informat = DATETIME22.3

         label = 'Period Created Month',

         (catx("_",(min(intnx('Month',(Datepart(Period_Create_Time)),0,'E')) ),Account_Number)) as Account_Policy_ID length = 8

            label = 'Account Policy ID',

          Period_Create_Time,

       Job_Type

   from &SYSLAST

   group by

     Account_ID,

   ;

quit;

[channa43 · Accepted solution]

Hi Thanks for the responses, now this has been resolved

Cheers