topic Re: How to match multiple selected codes in a Subfield in QlikView

How to match multiple selected codes in a Subfield

Thu, 28 Jan 2016 08:48:35 GMT

Hello Everyone,

I have these tables:

Catalog:

load * inline [

Code, Description

1000, New York

6000, Las Vegas

6005, San Jose

6007, San Francisco

0, No Name];

Airlines:

load * inline [

Airline, CodeList

1, '1000,6005,0'

2, '6007,6005'

3, 6000

4, '0,1000,6007,6000,6005'];

Catalog in ListBox1 and Airlines in ListBox2

I need to show in ListBox2 all the records selected in ListBox1 comparing them with CodeList field.

Example if in ListBox1 the user selected : 6000,6007. I need to Select in ListBox2 these records:

Airline, CodeList

2, '6007,6005'

3, 6000

4, '0,1000,6007,6000,6005'

Any ideas?

I attached the sample file.

Thanks in advance.

Re: How to match multiple selected codes in a Subfield

Anonymous — Thu, 28 Jan 2016 18:43:34 GMT

The CodeList values in the Airlines looks a bit weirdly formatted. almost as something went strange in a load from delimited text file.

Could you share source file that were loaded from ?

Re: How to match multiple selected codes in a Subfield

swuehl — Thu, 28 Jan 2016 19:34:29 GMT

Try something like

=Aggr(

If(Wildmatch(CodeList,$(=chr(39)&'*'&Concat(Code,'*'&chr(39)&','&chr(39)&'*')&'*'&chr(39))),Airline&'-'&CodeList)

, Airline)

Re: How to match multiple selected codes in a Subfield

sunny_talwar — Thu, 28 Jan 2016 20:16:16 GMT

Or may be this:

='(' & Concat({<CodeList = {$(=Chr(39) & Concat('*' & Code & '*', Chr(39) & ',' & Chr(39)) & Chr(39))}>}Airline, '|') & ')'

Re: How to match multiple selected codes in a Subfield

MarcoWedel — Fri, 29 Jan 2016 00:37:58 GMT

Hi,

one solution could be also:

or maybe

Catalog:
LOAD * Inline [
Code, Description
1000, New York
6000, Las Vegas
6005, San Jose
6007, San Francisco
0, No Name
];

Airlines:
LOAD Airline,
    Text(CodeList) as CodeList
Inline [
Airline, CodeList
1, '1000,6005,0'
2, '6007,6005'
3, 6000
4, '0,1000,6007,6000,6005'
];

tabLink:
LOAD Distinct
    CodeList,
    Trim(SubField(CodeList,',')) as Code
Resident Airlines;

hope this helps

regards

Marco