Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
yatra_qlik
Contributor
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
sunny_talwar

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

View solution in original post

3 Replies
rupamjyotidas
Specialist
Specialist

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

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
Contributor
Contributor
Author

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