Select only() on field based on Recno but Garbage after Group BY

[#1]

Hey there,

so I have this script, the description is unique to the rec num, and I can bring it in fine, but when I try to group by I get the garbage error. Here is the code below, am I approaching this incorrectly?

Garbage after statement

Test:

Load

  %CO_Area,

  %Cost_Center,

  only([Cost Center Description]),

  only(KeepChar(Right(%Cost_Center,4) & '-' & %CO_Area & [Cost Center Description],[Cost Center Description])) As Description,

  RecNo() As RecNum

  Group By

  RecNum

FROM

//Another QVD

[swuehl]

a) the GROUP BY clause should be put after the FROM

b) RecNum is created in the LOAD statement, so it's not available as field name from the input table source

c) you would need to apply aggregation functions to all fields not listed in the GROUP BY (e.g. there are missing aggregations for %CO_Area, %Cost_Center

d) why do you want to group by Recno() at all?

[#3]

Thank you so much for your input

see what is happening is, probably best illustrated by an example

a CO_AREA and Cost Center (ex.. 1010-1111 might have a description of EXAMPLE1

and another CO_AREA and Coster (ex.1020-1111 might have a description of EXAMPLE2

if I just load Cost Center Descriptions as is, both of these two rows/records will show EXAMPLE2 and I am hoping there might be a way to make sure each individual cost center description is associated with each row when imported from the QVD

Hope it explains it better!

[#4]

Oh and a quick follow up, where could I put the RecNo() function to count the records from the QVD and use it in the group by?, I can't do it in the load right, if I just load it I get the rec numbers fine and no crash and can see the column

just adding the group by crashes it, I tried aggregating the rest and then it tells me that RecNum is not a valid field or something to that effect haah

Once again thanks for your input!

[swuehl]

Not sure what you are trying to achieve, but IMHO, it doesn't make much sense to group by record number.

Your code does not seem to return the same Description without the group by clause, though I might not use input records similar to yours.

Load

  %CO_Area,

  %Cost_Center,

  [Cost Center Description],

  KeepChar(Right(%Cost_Center,4) & '-' & %CO_Area & [Cost Center Description],[Cost Center Description]) As Description

INLINE [

  %CO_Area,  %Cost_Center,Cost Center Description

  1010, 1111,EXAMPLE1

  1020, 1111,EXAMPLE2

];