Split field in multiple selectable Dropdown Boxes

[jacqueshol]

Hi there, 

While I have been using QlikView for several years now, my skills are very much at a beginner level. I would, therefore, be greatly appreciative of any assistance. I have a spreadsheet, a flat file, that I have imported into QlikView. 

For those who don't know, a truck refers to the front part of a truck combination (the engine), the tanker refers to the first trailer behind the truck, and the pup is a very small trailer that they sometimes hook behind the main trailer. So a truck combination consists of: Truck + Trailer + Pup.

Here is a small and simplified extract of my datafile that shows the cost associated to maintaining a vehicle:

Trans_ID	DATE	EQUIPMENT_TYPE	EQUIPMENT_No	SERVICE_TYPE	AMOUNT
1	20-Feb-2019	TRUCK	6131	MAINTENANCE	$382.00
2	23-Feb-2019	TRUCK	N4	MAINTENANCE	$11,525.58
3	23-Feb-2019	TRUCK	N4	MAINTENANCE	$993.00
4	01-Mar-2019	TANKER	135	MAINTENANCE	$990.00
5	03-Mar-2019	TRUCK	6134	MAINTENANCE	$14,800.00
6	03-Mar-2019	TRUCK	6134	MAINTENANCE	$286.00
7	04-Mar-2019	TRUCK	N3	MAINTENANCE	$2,033.00
8	07-Mar-2019	TANKER	135	TYRES	$2,422.00
9	07-Mar-2019	TANKER	135	WASHING	$920.00
10	07-Mar-2019	PUP	235	MAINTENANCE	$13,313.28
11	07-Mar-2019	PUP	235	WASHING	$690.00
12	10-Mar-2019	TRUCK	N4	MAINTENANCE	$9,954.38
13	12-Mar-2019	TRUCK	N1	TYRES	$440.00
14	13-Mar-2019	TANKER	180	MAINTENANCE	$11,200.00
15	14-Mar-2019	PUP	231	MAINTENANCE	$3,294.00
16	19-Mar-2019	TRUCK	N3	INSURANCE (ACC)	$495.00
17	19-Mar-2019	TRUCK	N3	WASHING	$200.00
18	20-Mar-2019	PUP	135	MAINTENANCE	$155.00
19	20-Mar-2019	PUP	135	MAINTENANCE	$2,691.00
20	20-Mar-2019	PUP	135	MAINTENANCE	$398.00

The script loads just fine. However, I want to split the EQUIPMENT_No Field into three dropdown boxes, i.e. one for Trucks, one for Tankers, and another for Pups. I have managed to do that by using the where statement in creating the dropdown box for each of these, e.g.

Trucks dropdown expression:

=if(match([Equipment_No], '6131', 'N4', '6134','N3','N1'), [Equipment_No])

I have used the same formula for tankers and pups. This method creates three separate dropdown boxes that work fine individually. The truck dropdown box only lists trucks, the tankers dropdown box only shows tankers, and so on. However, I cannot make multiple selections over the three dropdown boxes. In other words, I want to be able to select a vehicle combination. For example, I may want to select truck 6134 with tanker 135 and pup 235 from these three dropdown boxes. I know I can leave them in one big dropdown box and click-select them there, but it becomes difficult to scroll up and down as there are more than 200 different EQUIPMENT_No's in my complete history file.

I have attached the demo spreadsheet and QVW file. In the QVW file, you will see that I have been able to split the EQUIPMENT_No field (green dropdown) into the three desired dropdown boxes (yellow). However, if I select a vehicle in the Trucks dropdown box, I cannot select anything from the Tankers or Pups dropdown boxes without losing my Truck selection. I can, however, select multiple values from the ALL EQUIPMENT_No field (green). The values are indicated in the yellow boxes, though,  if I do a multi-select in my green dropdown box.

I want to present my file better by splitting the trucks from the tankers and pups and not having them all in one big pool. Is there anyone who could perhaps show me in the right direction? I would greatly appreciate it. 

Thanks in advance,

Jacques

[Marijn]

Hi Jacques,

I might have a solution for you. I've attached an altered qvw file to this reply, I'll explain what I did.

First I created three alternate states. Go to Settings->Document Properties->General->Alternate Sates. See here that there is group1, group2 and group3.

I added the alternate states to the three different list boxes, so group1 for Trucks, group2 for Tankers and group3 for Pups. Using the list boxes you created.

Created an expression that sums from all of the three groups:
Sum ({group1} AMOUNT) + Sum ({group2} AMOUNT) + Sum ({group3} AMOUNT)

Make the desired selections to see results.

Let me know if this solves your issue, or I overlooked something.

Regards,

Marijn

[jacqueshol]

Hi Marijn,

Wow, you were so quick to respond. Your solution works well if I select a value in all three dropdown boxes, in other words, if I select a value for each of the Trucks, Tankers, and Pups. However, that may not be the case. I may decide to select only choose a Truck. For example, if I want to see the service costs of only N1, I will select N1  in the Trucks dropdown box and nothing in Tankers and Pups. I should get $440 as that is the only value in the database. 

One other problem I noticed is that if I don't make any selections, the graphs show the values for the months as follows:

Feb:  $38,702

Mar:  $192,845

for a total of $231,547

However, if I do a simple filter in Excel on the data, the values should be:

Feb:  $12,901

Mar:  $64,282

for a total of  $77,183

In other words, the values are tripled when no selections are made.

Thanks again for your assistance, it is truly appreciated. I sincerely hope you can assist me with this small variation.

Take care,

Jacques

[Marijn · Accepted solution]

Hi Jacques,

Okay, so I made a set expression that does the trick, but is pretty much overly complicated:

=if(len(GetCurrentSelections(',',',',',','10','group1'))>0,
Sum ({group1} AMOUNT),0
)
+
if(len(GetCurrentSelections(',',',',',','10','group2'))>0,
Sum ({group2} AMOUNT),0
)
+
if(len(GetCurrentSelections(',',',',',','10','group3'))>0,
Sum ({group3} AMOUNT),0
)

+
if(len(GetCurrentSelections(',',',',',','10','group1'))
+
len(GetCurrentSelections(',',',',',','10','group2'))
+
len(GetCurrentSelections(',',',',',','10','group3'))=0,
Sum ( AMOUNT),0)

It basically checks if there are selections in the alternate states, then adds up the amount of the alternate states where there are selections. If there are no selections in the alternate states, it sums the regular amounts.

This has the drawbacks that if you make the same or overlapping selections in different alternate states, you will get wrong numbers. For example, if you select a truck in group1, and select a group of trucks in group2 (that includes the same truck  as selected in group one), you will get the truck amount summed twice.

As I said, this is an overly complicated solution, not very user friendly. There's probably a Qlik guru that has a better solution, but it works as far as I can see. See the attached QVW.

Kind regards,

Marijn

[jacqueshol]

Hi Marijn, I am astonished at how quickly you were able to assist me. The solution works perfectly. I am very grateful. The possible drawback you mentioned does not affect me, as I will never have the same values in group 2 as in group 1, for example. The three groups will always have distinct values. Thank you once again. Wishing you all the best.

Regards,

Jacques