topic Re: Combine the two fields and arrange first and second value based on alphabets in App Development

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

Kohli — Fri, 02 Apr 2021 07:43:23 GMT

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

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

Frank_Hartmann — Fri, 07 Dec 2018 09:43:52 GMT

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
];

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

marcus_sommer — Fri, 07 Dec 2018 14:52:11 GMT

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