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: 
umartareen
Creator II
Creator II

How to display GetFieldSelections only upto the previous selection ?

Hi,


I have a scenario where I need to get the field selections only up to the previous selection.


Example: As seen in the image below, upon selecting the employee names, Ben, Hill, John and Lou, my expression has to display only Ben, Hill and John and not Lou.

7.PNG

Similarly, If we select Ben, Hill, John, Lou and Maria, my output has to be this : Ben, Hill, John, Lou

How can this be achieved ? Any help will be much appreciated.

PFA of the Test Application.

Thanks,

Umar

1 Solution

Accepted Solutions
Kushal_Chawda

see this

=mid(Concat(DISTINCT Emp_Name,','),1,index(Concat(DISTINCT Emp_Name,','),',',-1)-1)

View solution in original post

12 Replies
Kushal_Chawda

see this

=mid(Concat(DISTINCT Emp_Name,','),1,index(Concat(DISTINCT Emp_Name,','),',',-1)-1)

umartareen
Creator II
Creator II
Author

Thank you very much Kushal ! Works perfect.

I also have a another issue with this, its that the values have to change dynamically as per the sort order of the list box !

I have changed the sort order now as seen in the image below,

8.PNG

As per this, the output has to be : Lou, John and Hill.

Is it possible to achieve this ?

Thanks,

Umar

Kushal_Chawda

Yes, you can but you need to define the sort order in the script either ascending or descending. In your case you have choose descending so, you have to do order by descending in the the script see below

Table:
load * Inline [
Emp_ID, Emp_Name, Budget,Sales
1, Tom, 100, 50
2, John, 200, 60
3, Hill, 300, 70
4, Ben, 400, 500
5, Maria, 500, 600
6, Lou, 600, 1000
7, Stu,400, 350
8, Vlad,600. 500
]
;

New:
LOAD *,
AutoNumber(Emp_Name) as Sort
Resident Table
Order by Emp_Name desc;

DROP Table Table;

Define your Sort filed as Sort value in Concat fucntion like below

=mid(Concat(DISTINCT Emp_Name,',',Sort),1,index(Concat(DISTINCT Emp_Name,',', Sort),',',-1)-1)

umartareen
Creator II
Creator II
Author

It does not seem to work ! Maybe I am going wrong somewhere.

Could you please post an example ?

Thanks,

Umar

Kushal_Chawda

I have already attached the application. You can see the script as well

Table:
load * Inline [
Emp_ID, Emp_Name, Budget,Sales
1, Tom, 100, 50
2, John, 200, 60
3, Hill, 300, 70
4, Ben, 400, 500
5, Maria, 500, 600
6, Lou, 600, 1000
7, Stu,400, 350
8, Vlad,600. 500
]
;

New:
LOAD *,
AutoNumber(Emp_Name) as Sort
Resident Table
Order by Emp_Name desc;

DROP Table Table;




=mid(Concat(DISTINCT Emp_Name,',',Sort),1,index(Concat(DISTINCT Emp_Name,',', Sort),',',-1)-1)

tamilarasu
Champion
Champion

No need sort the field. Try like below.

=Replace(GetFieldselections(FieldName,', '), ', '& SubField(GetFieldSelections(FieldName,', '),', ',-1),'')

Kushal_Chawda

or simply use without sorting

=mid(GetFieldSelections( Emp_Name,','),1,index(GetFieldSelections(Emp_Name,','),',',-1)-1)

umartareen
Creator II
Creator II
Author

Perfect Nagaraj ! Thank you very much.

Now I do not know which answer to mark as correct since both are right !

Will go with Kushal for the timely response.

Regards,

Umar

tamilarasu
Champion
Champion

Umar,

That's okay. I don't mind it and I really care whether my suggestion helps you or not in any way. Happy weekend.