topic Re: Select only() on field based on Recno but Garbage after Group BY in QlikView

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

Sat, 18 Jun 2016 15:44:36 GMT

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

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

swuehl — Sat, 18 Jun 2016 19:19:03 GMT

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?

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

Sat, 18 Jun 2016 22:35:53 GMT

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!

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

Sat, 18 Jun 2016 22:50:56 GMT

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!

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

swuehl — Sun, 19 Jun 2016 11:29:35 GMT

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

];