topic Re: How to order Excel File In Load Script in QlikView

How to order Excel File In Load Script

Thu, 23 Jul 2015 07:04:51 GMT

hello Dears

I want Order Excel File In Load Script

But Get Error.

Plz Help Me

Populations:

Load *, Num( Population) as pop;

LOAD Rank,

[Country (or dependent territory)] as country,

Date,

Population,

[% of world population],

Source

FROM

TB01_20150721_222616.xls ;

Re: How to order Excel File In Load Script

manojkulkarni — Thu, 23 Jul 2015 07:09:11 GMT

Pls can elaborate what's the requirement. Do you want to sort the excel data ?

Re: How to order Excel File In Load Script

Thu, 23 Jul 2015 07:22:15 GMT

no ,i want load Excel File Into Qlikview

And in load Script Order by Country Or Rank ,...

Re: How to order Excel File In Load Script

Kushal_Chawda — Thu, 23 Jul 2015 07:29:15 GMT

Something like this

Populations:

Load *, Num( Population) as pop;

LOAD Rank,

[Country (or dependent territory)] as country,

Date,

Population,

[% of world population],

Source

FROM

TB01_20150721_222616.xls ;

Population_New:

noconcatenate

load *

resident Populations

order by country;

drop table Populations;

Re: How to order Excel File In Load Script

Thu, 23 Jul 2015 07:55:06 GMT

Thanks,

This code is similar to code

But there is still a problem

Populations:

LOAD Rank,

[Country (or dependent territory)] as country,

Date,

Population,

[% of world population],

Source

FROM

[https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population]

(html, codepage is 1256, embedded labels, table is @1);

PopulationsOrder:

noconcatenate

Load * resident Populations order by country asc;

Drop table Populations;

---------------------------------------------------------------------------------------

Result Is Not Valid

Re: How to order Excel File In Load Script

Ralf-Narfeldt — Thu, 23 Jul 2015 08:00:25 GMT

If you check with preview in the Table Viewer you can see that they have been loaded ordered by country.

But the Table Box you display it in here is most likely set to sort on %population, check the properties.

Re: How to order Excel File In Load Script

Thu, 23 Jul 2015 08:04:40 GMT

In your image, you are sorting by '% of world population' as denoted by the ^ arrow on the field.

Using order by affects the load order of the data not the final display order you decide within an object. You need to use 'load order' in the sorting option of the object.

hope that helps

Joe

Re: How to order Excel File In Load Script

manojkulkarni — Thu, 23 Jul 2015 08:10:31 GMT

Unless there is a specific reason to sort data while loading,it is not necessary to load data in order w.r.t any columns. if you are doing some aggregate at script level then this is necessary.

Otherwise sorting can be done at chart level on different combinations.

Re: How to order Excel File In Load Script

Thu, 23 Jul 2015 08:34:42 GMT

I Used Table Box ,only Show Data.

but i want sort in Load Script.(without property in chart)

but But does not apply correctly

I want a piece of code that I wrote to chart settings have priority (The highest priority With load Or Resident Load)

-----------------------------------------------------------------

Populations:

LOAD

country,

Date,

Population,

Rank,

Source

FROM TB01_20150721_222616.xls

(biff, embedded labels, table is [Sheet1$]);

PopulationsOrder:

noconcatenate

Load *

resident Populations order by Rank desc;

Drop table Populations;

-----------------------------------------------------------------

Re: How to order Excel File In Load Script

Ralf-Narfeldt — Thu, 23 Jul 2015 08:47:00 GMT

Please check in File > Table Viewer, right click on the PopulationsOrder table and select Preview.

There you will see the true load order which should be as specified by Rank in this case.

For the Table Box, right click and select Properties, and go to Sort tab.

make sure that Load Order is the only selected option.

Any other order selected will override the load order.

Re: How to order Excel File In Load Script

Thu, 23 Jul 2015 08:57:38 GMT

Thank you,in

File > Table Viewer Data Load Correctly Very good.

in the Table Box ==> Properties, ==>Sort tab.

Deactivate All Fields in Sort ,Only Rank Load Order By Original Value.

But Result Not Correct Rank By DESC (Load Script)

in Table Viewer

Re: How to order Excel File In Load Script

sasiparupudi1 — Thu, 23 Jul 2015 13:34:03 GMT

Promote the rank dimension to the top and you will get your order . If you want it to be sorted on country, promote it to the top..

HTH

Re: How to order Excel File In Load Script

sasiparupudi1 — Thu, 23 Jul 2015 18:28:22 GMT

Any luck?

Sasi

Re: How to order Excel File In Load Script

Thu, 23 Jul 2015 19:03:10 GMT

hello

thank you

But did not resolve my issue

I want Only sort In Load Script NO Visual .

Finally , And I want on the record Sort

Re: How to order Excel File In Load Script

Qrishna — Thu, 23 Jul 2015 19:19:50 GMT

Hi Leila.

You can sort by country as well as sort by rank.

see attached.

Re: How to order Excel File In Load Script

Ralf-Narfeldt — Fri, 24 Jul 2015 05:39:32 GMT

Try renaming the Rank field in the Resident load.

Then you can change the name back later.

Populations:

LOAD

country,

Date,

Population,

Rank,

Source

FROM TB01_20150721_222616.xls

(biff, embedded labels, table is [Sheet1$]);

PopulationsOrder:

noconcatenate

Load

country,

Date,

Population,

Rank as rank,

Source

resident Populations order by Rank desc;

Drop table Populations;

Rename field rank as Rank;

Re: How to order Excel File In Load Script

Qrishna — Fri, 31 Jul 2015 18:17:10 GMT

Any Luck?