topic Re: group by FirstSortedValue in QlikView

group by FirstSortedValue

entsh — Sat, 18 Sep 2021 11:27:49 GMT

in edit script i used this code

select

code,name,number from....

the result is:

code	name	number
5	teh	500
6	teh	600
7	teh	300
4	ker	100
5	ker	200
6	ker	400
8	un	500

but i want this

for each name just show the maximum code:(code for edit script)

code	name	number
7	teh	300
6	ker	400
8	un	500

would u please help me?

Re: group by FirstSortedValue

Taoufiq_Zarra — Sat, 18 Sep 2021 12:20:51 GMT

@entsh Maye be :

Data: LOAD * INLINE [ code, name, number 5, teh, 500 6, teh, 600 7, teh, 300 4, ker, 100 5, ker, 200 6, ker, 400 8, un, 500 ]; output: noconcatenate load Max(code) as code,name,FirstSortedValue(number,-code) as number resident Data group by name; drop table Data;

output:

Re: group by FirstSortedValue

AshutoshBhumkar — Sat, 18 Sep 2021 14:58:17 GMT

Hello,

Another way is to create flag. Though the way @Taoufiq_Zarra has shown is absolutely correct. 🙂

Table:
Load code, name,number,code&'-'&name as codeName;
Load * Inline [
code,name,number
5,teh,500
6,teh,600
7,teh,300
4,ker,100
5,ker,200
6,ker,400
8,un,500
];

NoConcatenate
Table2:
Load
Max(code)&'-'&name as codeName,
'1' as Flag
resident Table group by name;

Thanks,

Ashutosh

Re: group by FirstSortedValue

MarcoWedel — Sat, 18 Sep 2021 21:04:40 GMT

Hi,

another solution might be to purge unnecessary lines with a right join like this:

If your data is loaded in table1, then

Right Join (table1) LOAD name, Max(code) as code Resident table1 Group By name;

hope this helps
regards
Marco