topic error with group by on a resident table in QlikView

error with group by on a resident table

Fri, 10 Feb 2012 00:25:47 GMT

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.

error with group by on a resident table

jagan — Fri, 10 Feb 2012 05:20:21 GMT

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.

error with group by on a resident table

vijay_iitkgp — Fri, 10 Feb 2012 05:26:01 GMT

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

error with group by on a resident table

Fri, 10 Feb 2012 14:52:39 GMT

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]

error with group by on a resident table

jagan — Sat, 11 Feb 2012 05:06:21 GMT

Hi,

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

Regards,

Jagan.