topic Re: Derive two new columns from existing one column in QlikView

Derive two new columns from existing one column

nihhalmca — Wed, 29 Jun 2022 18:00:06 GMT

Hi All -

How to derive two new columns from existing one column based on condition.

For instance:

Source:

Expected Result: Splitting "AirPort" column as two columns like 1. Departure Airport 2. Arrival Airport

Thank you.

Re: Derive two new columns from existing one column

MarcoWedel — Wed, 29 Jun 2022 18:10:16 GMT

Based on which key do you assign the arrival to the corresponding departure?

Re: Derive two new columns from existing one column

nihhalmca — Thu, 30 Jun 2022 05:08:58 GMT

Thanks for response. Do not have key field. Data is in order like destination then origin and again destination and origin so on... have to go record by record.

For instance: Line number 1 is destination, Line2 is Origin, Line3 is Destination, Line4 is Origin sequence is this way.

Thanks.

Re: Derive two new columns from existing one column

MarcoWedel — Thu, 30 Jun 2022 16:57:25 GMT

then maybe one solution could be:

table1: LOAD Div(RecNo()+1,2) as ID, * Inline [ Line, AirPort Departure, MUM Arrival, LON Departure, NYK Arrival, HYD Departure, CHE Arrival, DEN ]; table2: Generic LOAD ID, Line&' Airport', AirPort Resident table1;

Re: Derive two new columns from existing one column

nihhalmca — Fri, 08 Jul 2022 08:50:20 GMT

Thanks Marco. If I add one more column to my source what should be the change in "Code".

Actually above code will assign same number (ID) for two records same way I need to assign sequence like below.

Updated source:

NOTE: Actually there are 15 more columns after these 3 columns (above) however these 3 columns and 2 columns (derived columns - Departure airport and Arrival airport) are my "composite key" to link with data model.

Thank you.

Re: Derive two new columns from existing one column

MarcoWedel — Sat, 09 Jul 2022 13:50:12 GMT

maybe like this?

table1: LOAD RecNo() as RecNo, * Inline [ Line, Route, AirPort Departure, A, MUM Departure, B, MUM Departure, C, MUM Arrival, A, LON Arrival, B, LON Arrival, C, LON Departure, A, NYK Departure, B, NYK Departure, C, NYK Arrival, A, HYD Arrival, B, HYD Arrival, C, HYD Departure, A, CHE Departure, B, CHE Departure, C, CHE Arrival, A, DEN Arrival, B, DEN Arrival, C, DEN ]; table2: LOAD *, Div(RecNo()+1,2) as ID Resident table1 Order By Route, RecNo; DROP Table table1; table3: Generic LOAD ID, Line&' Airport', AirPort Resident table2;

Re: Derive two new columns from existing one column

nihhalmca — Tue, 12 Jul 2022 07:12:14 GMT

Thank you.