Evaluating Sub-string from a field ?

[yatra_qlik]

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. 

[rupamjyotidas]

Subfiled should help in the script

subfield(airline, '|' ) as airlinecode

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

[sunny_talwar · Accepted solution]

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;

[yatra_qlik]

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