How to fetch Field Value Based on another field value within same column ?

[prahlad_infy]

Hi Experts ,

In a table there is a field (field1)  which has one of the field value , let say Juno . This field value (Juno) is present in multiple rows .

So my requirement is  to fetch all the field value which is immediate next to field value Juno and create two different table .

Thank You All .

[vinieme12 · Accepted solution]

Table1:
load *,if(wildmatch(peek(field1),'juno'),field1,null()) as JUNO_ALL Inline [
field1
ABC
TVC
JUNO
client1
pqr
ppm
juno
dax
juno
rexx
];

Left Keep(Table1)
Table2:
load *,rowno() as rowno Inline [
JUNO_ALL
ABC
TVC
JUNO
client1
pqr
ppm
juno
dax
juno
rexx
]
Where Exists(JUNO_ALL);

drop field JUNO_ALL from Table1;

Table3:
load JUNO_ALL as JUNO_FIRST Resident Table2
Where rowno=1;

exit Script;

[prahlad_infy]

Thank You vineet sir . 

Just one comment - JUNO_ALL  is hardcorded . In realtime this word can be anything . 

Had it been dynamic , that would have been complete .