Number rows at the same time they are loaded from excel

[#1]

I have an excel that is loaded from the script.

I would like to number each row loaded starting from 1 but when a field matches some strings I want to number them as last.

For example, suppose I have following table:

Name     Surname

Charles  Pen

David     Coy

Anne      Flyp

Paul       Grant

John       Flint

and so on

I want to obtain a table like this (for example I want Anne and Charles to be numbered as last ones as below):

Name     Surname  Order

Paul       Grant         1

John       Flint           2

David     Coy            3

Anne      Flyp           4

Charles  Pen           5

Is it possible to do it at the same time the excel is loaded into table? Or first load excel into temporary table and then make it resident an order it.

With below approach I get Anne and Charles at the end, but not numbered correctly.

[Temp Table]:

LOAD

  Name,

  Surname,

  If(Upper(Trim(Name))='Anne', 2, If(Upper(Trim(Name)='Charles', 1, RecNo()+3)) as Order

FROM

  [$(QvDocumentBaseName)\MyExcel.xlsx]

  (ooxml, embedded labels, table is Sheet1);

[Final Table]:

LOAD

      *

Resident [Temp Table]

Order By Order desc;

DROP Table [Temp Table];

[swuehl]

I would do it like

LOAD Name  as NamesEnd

FROM NamesListToPutOnEnd;

YourData:

LOAD Name,

          Rowno() as Order

FROM ExcelFile

WHERE NOT EXISTS(NamesEnd, Name);

CONCATENATE (YourData)

LOAD Name

          Rowno() as Order

FROM ExcelFile

WHERE EXISTS(NamesEnd, Name);

DROP FIELD NamesEnd;

[#3]

Sunny, your solution works like a charm!

One thing, Is it possible from resident LOAD to get the maximum RecNo value and reassign Max(RecNo)+1 and Max(RecNo)+2 to 'Anne' and 'Charles' respectively?

[sunny_talwar]

Awesome , I am glad it did.

[sunny_talwar]

May be like this:

[Temp Table]:

LOAD

  Name,

  Surname,

  If(Upper(Trim(Name))='ANNE', 2, If(Upper(Trim(Name)='CHARLES', 1, RecNo()+3)) as Temp_Order

FROM

  [$(QvDocumentBaseName)\MyExcel.xlsx]

  (ooxml, embedded labels, table is Sheet1);

Max:

LOAD Max(Temp_Order) as Max

Resident [Temp Table];

LET vMax = Peek('Max');

DROP Table Max;

[Final Table]:

LOAD *,

          RecNo() as Order,

          Pick(Match(Upper(Trim(Name)), 'ANNE', 'CHARLES'), $(vMax) + 1, $(vMax) + 2) as FieldName

Resident [Temp Table]

Order By Temp_Order desc;

DROP Table [Temp Table];

DROP Field Temp_Order;