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

[Kohli]

I have table like

Table1:

Flight, Sector,   Pax,     Fare

123,   djhdww,    1,       1200

123,   bdwehj,     1,       1500

123,   jasadhj,   N/A,      2000

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

123,   jasadhj,   N/A,      2000

124,   sgshgs,    N/A,     5000

[kfoudhaily]

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

[dplr-rn]

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

[mdmukramali]

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:

LOAD * Inline

[

Flight,Sector,Pax,Fare

123,djhdww,1,1200

123,bdwehj,1,1500

123,jasadhj,N/A,2000

124,sgshgs,N/A,5000

]

;

NoConcatenate

Connected:

LOAD *

Resident Table1

Where Pax=1;

NoConcatenate

Direct_Flight:

LOAD *

Resident Table1

Where Pax<>1;

DROP Table Table1;

[kfoudhaily]

table: // your table loaded inline

LOAD * INLINE [

Flight, Sector,   Pax,     Fare

123,   djhdww,    1,       1200

123,   bdwehj,     1,       1500

123,   jasadhj,   N/A,      2000

124,   sgshgs,    N/A,     5000

];

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

load Distinct

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):

LOAD * INLINE [

key, Flight

$(i), $(value)

];

left join

load *

resident table;

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

drop table result_table$(i);

NEXT i;

drop tables table,temp;

[Anil_Babu_Samineni]

You can restricted using Where Condition from Each field?