topic Re: Combined List Box? in QlikView

Combined List Box?

peterderrington — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Hoping someone can point me in the right direction....

I have an excel Spread sheet that shows information about transactions; each transaction can have one (or two) of 3 possible supervisors. I can currently show which of the supervisors relate to which transaction but what I was hoping to be able to do was create one list box that will allow me to pick just the transactions that that supervisor relates too.

Example of data:

ID Object Supervisor 1 Supervisor 2 Supervisor 3

1 Frog Bill Fred None

2 Ball None Bill John

3 Bat John None Steve

I'd like a list box that would just show:

Supervisor

Bill

Fred

John

Steve

Any ideas?

Thanks,

Peter

Re: Combined List Box?

sunny_talwar — Tue, 05 Jun 2018 13:54:33 GMT

Use The Crosstable Load to transform your data

Re: Combined List Box?

sunny_talwar — Tue, 05 Jun 2018 13:56:58 GMT

Like this

Table:

CrossTable (Level, Supervisor, 2)

LOAD * INLINE [

ID, Object, Supervisor 1, Supervisor 2, Supervisor 3

1, Frog, Bill, Fred, None

2, Ball, None, Bill, John

3, Bat, John, None, Steve

];

Re: Combined List Box?

Peter_Cammaert — Tue, 05 Jun 2018 13:58:10 GMT

Sunny can probably suggest a merged-field listbox trick, but I know of a simple script extension like:

SupervisorN:

LOAD Supervisor1 AS SupervisorN,

RESIDENT ExampleOfDataTable;

CONCATENATE(ExampleOfDataTable)

LOAD Supervisor2 AS SupervisorN,

RESIDENT ExampleOfDataTable;

CONCATENATE(ExampleOfDataTable)

LOAD Supervisor3 AS SupervisorN,

RESIDENT ExampleOfDataTable;

Now you can create a Listbox from field SupervisorN that shows all possible transaction for which the selection is present in any combination of Supervisor1-3 fields.

Re: Combined List Box?

peterderrington — Tue, 05 Jun 2018 15:04:01 GMT

Thanks Sunny,

I'll give this a try. Do I need to put it in the 'Edit Script' Section? What field would I select in the list box field selector?

Re: Combined List Box?

sunny_talwar — Tue, 05 Jun 2018 15:06:18 GMT

Yes, this will go in the script. And for selection, you will be using Supervisor field.

Re: Combined List Box?

peterderrington — Tue, 05 Jun 2018 15:25:28 GMT

Excellent, ok.

I don't need to go through each line though do I? Theres over 500 IDs and only a combination of about 10 Supervisors.

Re: Combined List Box?

sunny_talwar — Tue, 05 Jun 2018 16:02:03 GMT

I don't think so...

Re: Combined List Box?

rwunderlich — Tue, 05 Jun 2018 16:53:11 GMT

Don't put the inline data as Sunny showed in the script as a demonstration. You CrossTable load will read from the same source where you get your data.

-Rob

Re: Combined List Box?

peterderrington — Fri, 08 Jun 2018 13:42:27 GMT

Sorry,

I've been away at a conference the past couple of days and this is the first chance I've had to look at this.

I have added the below element to the script and now when I add the List Box there is a new Field called 'Supervisor' which when I select, then doesn't show anything in the list box.

Two of the fields mentioned in the script now show little key symbols in them which I think means I'm a big step closer to doing what I want but obviously its not quite working.

Any ideas?

CrossTable (Level, Supervisor,2)
LOAD *INLINE [Supervisor 8-5, Supervisor 4:30-8, Supervisor 8-8];