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];

[sunny_talwar]

Unfortunately, order by statement doesn't work on Excel load. So you will have to load the Excel and take a resident load for a customized Order of your data.

[#3]

Yes, That was what I have done, but my problem is to number the rows being loaded from excel, and number the ones I want as the last ones. I am trying to number them at the same time I load them from excel and I create a new field called Order that keeps this number (1..N), and finally, using resident, I obtain the final table ordered by the field Order.

But how can I number the rows I want to be the last ones?

[sunny_talwar]

What is the logic for keeping Anne and Charles as the last on the order. I am not completely sure how you are deciding the order. Can you may be elaborate on the thought process a little more?

[effinty2112]

In your excel script you can add a line such as

RecNo() as ExcelOrder

to keep track of the order of the records in the excel source.

cheers

Andrew

[#6]

Yes, but how can I tell qlikview that Anne and Charles rows to be the last ones?

[sunny_talwar]

What is the logic behind making them last? I mean in this example we might be able to force it, but in general there needs to be some logic which needs to be followed, right?

[#8]

I want to put these rows at the end because I have a list, and in the list, I want them to appear at the end. This is the objective. I have edited the question, with an approach.

[sunny_talwar · Accepted solution]

What if you do 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);

[Final Table]:

LOAD *,

          RecNo() as Order

Resident [Temp Table]

Order By Temp_Order desc;

DROP Table [Temp Table];

DROP Field Temp_Order;

[effinty2112]

Hi Tony,

              If you know beforehand that you want Anne and Charles to be last then

Match(Name, 'Anne','Charles')

will return 1 for Name = Anne, 2 for Name = Charles and 0 for other names. You could sort by this expression.

Cheers

Andrew