Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
entsh
Contributor II
Contributor II

last record with condition in edit script

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

 

1 Solution

Accepted Solutions
Raja2022
Contributor III
Contributor III

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;

Raja2022_0-1645894083305.png

Hope this helps..

View solution in original post

7 Replies
Vegar
MVP
MVP

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
Contributor III
Contributor III

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;

Raja2022_0-1645894083305.png

Hope this helps..

entsh
Contributor II
Contributor II
Author

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
Contributor III
Contributor III

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
Contributor III
Contributor III

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

entsh
Contributor II
Contributor II
Author

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
Contributor III
Contributor III

You are welcome  and glad able to help...

Enjoy your day...