topic Re: Order by in QlikView

Order by

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

This is my script

T1:

LOAD

RowNo() as ROW,

ID,

DATE,

PRODUCT,

SALES

FROM

(ooxml, embedded labels, table is Sheet6) ;//Where RowNo()<20 Order by PRODUCT desc;

NoConcatenate

T2:

load

RowNo() as RANK,

ID,

DATE,

PRODUCT,

SALES

Resident T1 Where RowNo()<20 Order by PRODUCT desc;

DROP Table T1;

I am Writing Order By statement in Script its not working

if I am Writing Order By statement in Resident table its working

Can any one Explain me Why this Behaving like this

Regards,

Mahesh

Re: Order by

tamilarasu — Tue, 19 Jul 2016 06:42:57 GMT

Hi Mahesh,

You can do a order by only in resident load of data. It won't work for direct load of QVD and flat files. You can see this in help file.

Re: Order by

MindaugasBacius — Tue, 19 Jul 2016 06:44:18 GMT

From QlikView Help:

That's the answer I guess.

Re: Order by

qlikview979 — Tue, 19 Jul 2016 06:46:35 GMT

OK,Thanks Bro

I have one More Doubt

Is it Rank Function will work with Agg() Function.

Regards

Re: Order by

qlikview979 — Tue, 19 Jul 2016 06:47:36 GMT

Hi Mindaugas,

Thanks,

Is it Rank Function will work with Agg() Function.

Regards

Re: Order by

tamilarasu — Tue, 19 Jul 2016 06:49:16 GMT

Yes. I guess you are asking like below expression.

=Aggr(If(Rank(Sum(Sales))<=10,Customer),Customer)

Re: Order by

qlikview979 — Tue, 19 Jul 2016 06:55:08 GMT

Hi Bro,

If I am Doing like this i am getting

sum({<PRODUCT={"=Rank(SUM(SALES))<=10"}>}SALES)

I tried with your Expression i am not getting any thing

Re: Order by

tamilarasu — Tue, 19 Jul 2016 07:13:50 GMT

You can use either calcualuated dimension or expression to acheive the result. What I have suggested is calculated dimension formula.

Try like below.

Calculated dimension:

=Aggr(If(Rank(Sum(SALES))<=10,YourDimensionName),YourDimensionName)

Expression:

Sum(SALES)

Re: Order by

qlikview979 — Tue, 19 Jul 2016 07:23:20 GMT

Hi,

Thanks Bro,

I got It

Regards

Mahesh