last record with condition in edit script

[entsh]

hello

please consider below table:

id	name	choice	family
1	a	teh	cili
2	a	teh	dili
3	a	teh	vili
4	b	teh	nili
5	b	ker	fili
6	c	teh	jili
7	c	teh	mili
8	c	ker	bili
9	d	teh	rili

for each name show only max id and choice ="teh"

if the max id for each name choice="ker " do not show the name and the row

(i need code in edit script)

result:

id	name	choice	family
3	a	teh	vili
9	d	teh	rili

[Vegar]

Maybe you can do like this.

//list name with 'ker'

LOAD distinct name 

FROM Source

WHERE choice = 'ker';

// the max id  per name where 'teh'

inner Join

Load

name,

Max(id) as id

From Source 

where choice = 'teh'

Group By name;

// Join on all the details for the identified ids.

Left Join LOAD * FROM Source;

[Raja2022 · Accepted solution]

Here is one of the way you can do it:

Data:
Load * inline [
id1, name1, choice1, family1
1, a, teh, cili
2, a, teh, dili
3, a, teh, vili
4, b, teh, nili
5, b, ker, fili
6, c, teh, jili
7, c, teh, mili
8, c, ker, bili
9, d, teh, rili
];

Temp:
Load maxString(name1) as name
Resident Data
where choice1 = 'ker'
group by name1;

Load
max(id1) as id,
MaxString(name1) as name,
MaxString(choice1) as choice,
MaxString(family1) as family
Resident Data
where not Exists(name, name1)
group by name1;

drop table Temp;
drop table Data;

Hope this helps..

[entsh]

thanks a lot

it almost done but

i got this:

id	name	choice	family
3	a	teh	vili
9	d	teh	rili
-	b	-	-
-	c	-	-

i dont want to load the row 3 and 4 (name b & c are empty)

i want this result:

id	name	choice	family
3	a	teh	vili
9	d	teh	rili

[Raja2022]

Please check your code once again. I am not getting the b, c records. In a blank qvw document copy and paste the code that I gave above and run it and see. You will only get id 3 and 9. 

[Raja2022]

Attached is my .qvw dashboard and please take a look.

[entsh]

thanks a lot ,its true

i got extra rows because i didn't use these rows

drop table Temp;
drop table Data;

i thought its not necessary !!

[Raja2022]

You are welcome  and glad able to help...

Enjoy your day...