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: 
Kohli
Creator II
Creator II

Combine the two fields and arrange first and second value based on alphabets

I have table like

Flight,  From,  To

F11, MUM, DEL

F22, DEL,HYD

F33, HYD, DEL

F13, BNG, DEL

F15, MUM, CHN

F16, IXI, DEL

 I want output like these

Flight,   From,   To,  Sector,   Route

F11, MUM, DEL, MUM-DEL,  DEL-MUM   (COMPARE M and D,  AND 1st alphabet place first for route column)

F22, DEL, HYD,  DEL-HYD,     DEL-HYD

F33, HYD, DEL, HYD-DEL,   DEL-HYD

F13, BNG, DEL, BNG-DEL,    BNG-DEL

F15, MUM, CHN, MUM-CHN, CHN-MUM

F16, IXI, DEL, IXI-DEL,        DEL-IXI

 

 

 

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

Another approach which not only checked the first char else the whole string:

load *, pick(-(To precedes From) + 1, From & ' - ' & To, To & ' - ' & From) as Order;
LOAD * INLINE [   
    Flight,  From,  To    
    F11, MUM, DEL    
    F22, DEL,HYD    
    F33, HYD, DEL    
    F13, BNG, DEL    
    F15, MUM, CHN    
    F16, IXI, DEL
];

- Marcus

 

View solution in original post

2 Replies
Frank_Hartmann
Master II
Master II

try this:

Load *, From&'-'&To as Sector, 
	if(ord(left(From,1))<ord(left(To,1)),From&'-'&To, To&'-'&From) as Order;
LOAD * INLINE [   
    Flight,  From,  To    
    F11, MUM, DEL    
    F22, DEL,HYD    
    F33, HYD, DEL    
    F13, BNG, DEL    
    F15, MUM, CHN    
    F16, IXI, DEL
];

 

image.png

marcus_sommer

Another approach which not only checked the first char else the whole string:

load *, pick(-(To precedes From) + 1, From & ' - ' & To, To & ' - ' & From) as Order;
LOAD * INLINE [   
    Flight,  From,  To    
    F11, MUM, DEL    
    F22, DEL,HYD    
    F33, HYD, DEL    
    F13, BNG, DEL    
    F15, MUM, CHN    
    F16, IXI, DEL
];

- Marcus