topic load csv with sort in QlikView

load csv with sort

Anonymous — Mon, 14 Sep 2015 20:37:41 GMT

Hi guys,

Could you tell me if possible to load csv file with order by?

Thanks,

Re: load csv with sort

Digvijay_Singh — Mon, 14 Sep 2015 20:54:01 GMT

I think order can only be controlled after loading the file into QV, like below or may be other ways but it has to be loaded first so that QV functions can work -

Table1:

LOAD Country,

Customer,

Sales

FROM

Data.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

NoConcatenate

Output:

Load * Resident Table1 Order By Sales Desc;

Drop Table Table1;

Re: load csv with sort

Anonymous — Mon, 14 Sep 2015 20:56:05 GMT

I did like you suggested.

In 1 table it's not doable

Thanks,

Re: load csv with sort

Mon, 14 Sep 2015 21:07:44 GMT

Vladimir,

Not posible, "order by" function is only executable in resident table.

Regards.

Re: load csv with sort

Anonymous — Mon, 14 Sep 2015 21:08:46 GMT

Thank you for confirming

Re: load csv with sort

Digvijay_Singh — Mon, 14 Sep 2015 21:10:27 GMT

Yup, found few similar questions but don't see any direct option of loading and sorting in one go.

Re: load csv with sort

Mon, 14 Sep 2015 21:13:22 GMT

"order by is a clause used for sorting the records of a resident table before they are processed by the load statement. The resident table can be sorted by one or more fields in ascending or descending order. " References Manual 11.2 SR12 - Page 255.

Re: load csv with sort

Clever_Anjos — Mon, 14 Sep 2015 21:22:38 GMT

You can use a precedent load

Table1:

LOAD Country,

Customer,

Sales

Order By Sales Desc;

LOAD Country,

Customer,

Sales

FROM

Data.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

Re: load csv with sort

Tue, 15 Sep 2015 13:50:22 GMT

Clever,

You are sure? I understand with precedent load only use function "Where, While and Group By"

Regards.

Re: load csv with sort

Anonymous — Tue, 15 Sep 2015 13:57:44 GMT

Something is wrong. I need to add autonumber based on my sort.

CSV:

LOAD AutoNumber(TimeStamp) as Auto_Id,

SessionID,

Url,

TimeStamp,

ValidData

ORDER BY SessionId, [TimeStamp] ASC;

LOAD AutoNumber(TimeStamp) as Auto_Id,

SessionID as SessionId,

Url,

TimeStamp,

ValidData

FROM

(txt, codepage is 1251, embedded labels, delimiter is ',', msq)

where match(SessionID,$(vValue));