Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yatra_qlik
New Contributor

Evaluating Sub-string from a field ?

Hi Folks,

I need your help.

My Data structure is as follows

id        airline      
100     9W
101     9W|6E

102     9W|9W
103     6E
104     6E|6E|6E

105     9W|9W

where id is unique and auto-incremental. All airline codes are of 2 characters separated by a "|" character .

I have to find count of ids of each distinct airline
Final output should be
9W: 4 (100,101,102,105)

6E : 3  (101, 103, 104)
Every id is counted once even if  it has multiple airlines.

Please guide, what functions/approach should I use. 

1 Solution

Accepted Solutions

Re: Evaluating Sub-string from a field ?

I would create a linktable to get this done

Table:

LOAD * INLINE [

    id,      airline     

    100,    9W

    101,    9W|6E

    102,    9W|9W

    103,    6E

    104,    6E|6E|6E

    105,    9W|9W

];

LinkTable:

LOAD airline,

  SubField(airline, '|') as airline_list

Resident Table;

Capture.PNG

3 Replies
rupamjyotidas
Valued Contributor

Re: Evaluating Sub-string from a field ?

Subfiled should help in the script

subfield(airline, '|' ) as airlinecode



For each Airline Code you will have the ID, which you can count


Re: Evaluating Sub-string from a field ?

I would create a linktable to get this done

Table:

LOAD * INLINE [

    id,      airline     

    100,    9W

    101,    9W|6E

    102,    9W|9W

    103,    6E

    104,    6E|6E|6E

    105,    9W|9W

];

LinkTable:

LOAD airline,

  SubField(airline, '|') as airline_list

Resident Table;

Capture.PNG

yatra_qlik
New Contributor

Re: Evaluating Sub-string from a field ?

Thanks a lot Sunny and Rupam. It Worked.
Especially Sunny for attaching the qvf

Community Browser