topic Re: last record with condition in edit script in QlikView

last record with condition in edit script

entsh — Sat, 26 Feb 2022 10:11:13 GMT

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

Re: last record with condition in edit script

Vegar — Sat, 26 Feb 2022 14:57:15 GMT

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;

Re: last record with condition in edit script

Raja2022 — Sat, 26 Feb 2022 16:48:29 GMT

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..

Re: last record with condition in edit script

entsh — Sat, 26 Feb 2022 17:23:58 GMT

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

Re: last record with condition in edit script

Raja2022 — Sat, 26 Feb 2022 17:37:44 GMT

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.

Re: last record with condition in edit script

Raja2022 — Sat, 26 Feb 2022 17:41:54 GMT

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

Re: last record with condition in edit script

entsh — Sat, 26 Feb 2022 17:48:16 GMT

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 !!

Re: last record with condition in edit script

Raja2022 — Sat, 26 Feb 2022 18:15:07 GMT

You are welcome and glad able to help...

Enjoy your day...