topic Re: Number rows at the same time they are loaded from excel in QlikView

Number rows at the same time they are loaded from excel

Mon, 03 Oct 2016 15:36:08 GMT

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

Re: Number rows at the same time they are loaded from excel

sunny_talwar — Mon, 03 Oct 2016 15:44:13 GMT

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.

Re: Number rows at the same time they are loaded from excel

Mon, 03 Oct 2016 15:48:42 GMT

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?

Re: Number rows at the same time they are loaded from excel

sunny_talwar — Mon, 03 Oct 2016 15:51:58 GMT

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?

Re: Number rows at the same time they are loaded from excel

effinty2112 — Mon, 03 Oct 2016 15:56:08 GMT

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

Re: Number rows at the same time they are loaded from excel

Mon, 03 Oct 2016 15:57:24 GMT

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

Re: Number rows at the same time they are loaded from excel

sunny_talwar — Mon, 03 Oct 2016 16:02:59 GMT

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?

Re: Number rows at the same time they are loaded from excel

Mon, 03 Oct 2016 16:11:00 GMT

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.

Re: Number rows at the same time they are loaded from excel

sunny_talwar — Mon, 03 Oct 2016 16:13:19 GMT

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;

Re: Number rows at the same time they are loaded from excel

effinty2112 — Mon, 03 Oct 2016 16:15:36 GMT

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

Re: Number rows at the same time they are loaded from excel

swuehl — Mon, 03 Oct 2016 16:18:43 GMT

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;

Re: Number rows at the same time they are loaded from excel

Mon, 03 Oct 2016 20:54:34 GMT

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?

Re: Number rows at the same time they are loaded from excel

sunny_talwar — Mon, 03 Oct 2016 20:55:45 GMT

Awesome , I am glad it did.

Re: Number rows at the same time they are loaded from excel

sunny_talwar — Mon, 03 Oct 2016 21:45:18 GMT

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;