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?
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!
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!
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.
[Cost Center Description],
KeepChar(Right(%Cost_Center,4) & '-' & %CO_Area & [Cost Center Description],[Cost Center Description]) As Description
%CO_Area, %Cost_Center,Cost Center Description