4 Replies Latest reply: Oct 17, 2018 9:57 AM by Anil Samineni

# divide the single table into 2 tables based on Field values.

I have table like

Table1:

Flight, Sector,   Pax,     Fare

123,   djhdww,    1,       1200

123,   bdwehj,     1,       1500

124,   sgshgs,    N/A,     5000

output tables:

Connected:

Flight, Sector,   Pax,     Fare

123,   djhdww,    1,       1200

123,   bdwehj,     1,       1500

Direct Flight:

Flight, Sector,   Pax,     Fare

124,   sgshgs,    N/A,     5000

• ###### Re: divide the single table into 2 tables based on Field values.

do you know in advance how many values you can have in the field "flight"

• ###### Re: divide the single table into 2 tables based on Field values.

can i ask why you want to split the tables? why not just put a flag saying direct or not?

• ###### Re: divide the single table into 2 tables based on Field values.

Hi,

U can add if condition in the same Table like

If(pax=1,'Connected','Direct Flight') as FlightType

if you have some other requirement than u can try something like below:

Table1:

[

Flight,Sector,Pax,Fare

123,djhdww,1,1200

123,bdwehj,1,1500

124,sgshgs,N/A,5000

]

;

NoConcatenate

Connected:

Resident Table1

Where Pax=1;

NoConcatenate

Direct_Flight:

Resident Table1

Where Pax<>1;

DROP Table Table1;

• ###### Re: divide the single table into 2 tables based on Field values.

Flight, Sector,   Pax,     Fare

123,   djhdww,    1,       1200

123,   bdwehj,     1,       1500

124,   sgshgs,    N/A,     5000

];

temp: //a temp table to get all distinct values of field "Flight"

Flight as val

Resident table Order by Flight;

for i=1 to NoOfRows('temp') // count to the number of rows of last table

LET value = peek('val', \$(i), 'temp');

result_table\$(i):

key, Flight

\$(i), \$(value)

];

left join

resident table;

store result_table\$(i) into result_table\$(i).qvd (qvd);

drop table result_table\$(i);

NEXT i;

drop tables table,temp;

• ###### Re: divide the single table into 2 tables based on Field values.

You can restricted using Where Condition from Each field?