error with group by on a resident table

[#1]

Hi I am trying to reduce data by grouping on specific colums in the data [ which I already loaded from multiple text files ].

below script  failed saying general script error]

NEW_DATA:

add

LOAD    mid(@1,4,10) as column1 ,

                mid(@3,3,4) as column2,

               date#(@4,'DD-MON-YY') as column3,

            sum( @6) as column4,

               sum(@7) as column5

   RESIDENT  DATA

group by mid(@1,4,10), mid(@3,3,4), date#(@4,'DD-MON-YY');

DROP table DATA;

I wasnt sure if this date format existed so I tried the script below. [ it failed saying general script error]

NEW_DATA:

add

LOAD    mid(@1,4,10) as column1 ,

                mid(@3,3,4) as column2,

                   @4 as column3,

                 sum( @6) as column4,

               sum(@7) as column5

   RESIDENT  DATA

group by mid(@1,4,10), mid(@3,3,4), @4;

DROP table DATA;

i sed a work around

NEW_DATA:

add

LOAD    mid(@1,4,10) as column1 ,

                mid(@3,3,4) as column2,

                   @4 as column3,

                  @6 as column4,

               @7 as column5

   RESIDENT  DATA;

and used the chart object to group the data.

I would like to understand the issues with the  first two scripts.

[jagan]

Hi,

I didn't found any issue in your script.  I tried the below script, which is similar to your script.  It works fine.

Temp:

LOAD * INLINE [

    @Dept, @Value

    ABC, 100

    ABC, 34

    ABC, 453

    BCD, 3454

    BCD, 343

    BCD, 232

    BCD, 543

    CDE, 2344

    CDE, 543

];

NOCONCATENATE

Values:

LOAD

    Mid(@Dept, 2,3) AS Dept,

    Sum(@Value) AS Value

RESIDENT Temp

GROUP BY Mid(@Dept, 2,3);

DROP TABLE Temp;

Can you attach the sample file with script.

Regards,

Jagan.

[vijay_iitkgp]

Hi,

I domt think you can use functions in group by clause. Ist load and transform the data and then group by it on resident load.

Vijay

[#4]

Hi Vijay, is this limitation restricted to any specific functions. Since vijay is using mid in group by and its works fine[ as per vijay]

[jagan]

Hi,

Can you attach the file, so that it would be easier to find the issue.

Regards,

Jagan.