Filter by initial and final word

[vicente89]

Hello, I have a problem.

I need data for certain routes whose origin is MAD and destinations are: LPA TFN/TCI FUE ALC PMI MAH AGP PUJ HAV CUN SDQ.

I have a 'routing' filter in which I can filter by routes.
The problem is that many routes make stopovers and those stopover airports leave in the middle of the route.
Example: MAD-ALC, ALC-AGP, AGP-HAV.
What I want is that it filters me only for the ones that start with MAD and end with one of those 12 destinations.

In other occasions, I have used the regular expression *MAD*, which filters everything that contains the word MAD, but I can't find any regular expression that does the same but when it is only the first word; nor when it is the last one, to put the destinations.

ChatGPT tells me that to make it the first word MAD, I have to put ^MAD*, but I have tried it and it doesn't show me anything.

I do not have access to the script editor.

Does anyone know how to do it?

Thank you very much in advance.

[Happy_Mask_Salesman]

Buenas tardes Vicente.

Si lo que quieres es que te filtre por aquellas salidas que empiecen por MAD, con MAD* sería suficiente.

Mucho ojo, la sugerencia que te hace ChatGPT, no es para el filtrado de valores en Qlik, si no para expresiones regulares (lo digo por el ^MAD*, donde el ^ no forma parte de la sintaxis de Qlik).

• Salidas desde Madrid: MAD*
• Rutas que empiecen, pasen o lleguen a Madrid: *MAD*
• Llegadas a Madrid: *MAD

Un saludo.

[vicente89]

Buenas, no me funciona lo que me comentas, poniendo *MAD o MAD* no me aparece nada (con *MAD* si).

Y si hay rutas que empiezan y terminan en MAD, es como si no me cogiese esa expresión regular.
Adjunto imagenes.

[vincent_ardiet_]

You can try something like this:

Match([Route],'MAD*') And Match([Route],'*LPA','*TFN','*TCI','*FUE','*ALC','*PMI','*MAH','*AGP','*PUJ','*HAV','*CUN','*SDQ') 

[Happy_Mask_Salesman]

Te contestaré en inglés para que otros colaboradores o desarrolladores puedan corregirme, pero el truco recae en que debes escribir también el corchete y las primeras comillas dobles.

Based on the last screenshot you sent (3.PNG), I can see that it should be like this:

• Beginning with MAD:  {"MAD*
  This means every route that begins with {"MAD
• Containing MAD: *MAD*
• Ending with MAD: *MAD"}

This would be the solution at the time of using wildcards (comodines) filtering from selection panel.

In addition to @vincent_ardiet_ and thanks to the context of the third screenshot, I would modify Vincent's formula to also catch the double quotes and brackets.

Remember, it's not just MAD, it's including opening brackets and the first double quote, for the departures, *MAD* for stopovers and *MAD"} for arrivals, including the last double quote mark and closing bracket.

I hope this works out.

Regards,

[vincent_ardiet_]

And sorry, this is a wildmatch and not a match (of course).

[Happy_Mask_Salesman]

Very important! Nice one noticing it.

WildMatch() function as its name says, allows you to use wildcards in the search for the matched values.

Match() function has to be precisely that string of characters and doesn't allow the use of wildcards (*).

Regards,

[vicente89]

Good afternoon.

Nothing, so I get nothing, neither putting it in the search engine of the "routing" filter nor creating a new measure with that formula.

I attach images.

[vincent_ardiet_]

4.png -> try to use [Route] instead of [route]
5.png -> try to use wildmatch instead of match

[vicente89]

Yes, that's how it works.

So I get to know which ones start with MAD and which ones end with MAD.
But actually what I need to know is to filter by the following routes:

MAD-LPA, MAD-TFN, MAD-TCI, MAD-FUE, MAD-ALC, MAD-PMI, MAD-MAH, MAD-AGP, MAD-PUJ, MAD-HAV, MAD-CUN, MAD-SDQ.

The problem is that the first 3 initials is the origin and the last 3 the destination, but many of these routes have stopovers in between; so what I need is that it does not take into account the initials between origin and destination. I only need to filter by the lines that start with the first 3 letters and end with the last 3.

Would you know how it would be?