topic Re: How to get required order in table box in QlikView

How to get required order in table box

Thu, 02 Jan 2014 14:15:57 GMT

Hi,

I have a table box . In the edit script I have below one

Data:

LOAD

ID,

Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464',

'413172',

'415033',

'415623',

'454035',

'442363',

'455699',

'457311',

'458105',

'326600',

'455024',

'453906',

'1851492',

'455839',

'457788',

'330983',

'328251',

'372278',

'376328',

'369175',

'386311',

'426283',

'425362',

'424456',

'418055',

'426787',

'386026',

'387355',

'386161',

'418753',

'472508',

'1850066',

'423629',

'504005',

'382816',

'388291');

I need to have the Reference as above order in Table box.could anyone help me is that possible to create like above. I think I have to write expression in Sort tab is that correct?

Thanks.

Re: How to get required order in table box

Thu, 02 Jan 2014 14:31:23 GMT

Please any help???

Re: How to get required order in table box

Thu, 02 Jan 2014 14:34:02 GMT

If your database is MySQL you could use Field(), and use RowNo() in the LOAD statement as a sort key e.g.

Data:

LOAD

RowNo() as SortOrder,

ID,

Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464',

'413172',

'415033',

'415623',

'454035',

'442363',

'455699',

'457311',

'458105',

'326600',

'455024',

'453906',

'1851492',

'455839',

'457788',

'330983',

'328251',

'372278',

'376328',

'369175',

'386311',

'426283',

'425362',

'424456',

'418055',

'426787',

'386026',

'387355',

'386161',

'418753',

'472508',

'1850066',

'423629',

'504005',

'382816',

'388291')

ORDER BY FIELD(Reference,

'371284',

'373896',

'374493',

'411464',

'413172',

'415033',

'415623',

'454035',

'442363',

'455699',

'457311',

'458105',

'326600',

'455024',

'453906',

'1851492',

'455839',

'457788',

'330983',

'328251',

'372278',

'376328',

'369175',

'386311',

'426283',

'425362',

'424456',

'418055',

'426787',

'386026',

'387355',

'386161',

'418753',

'472508',

'1850066',

'423629',

'504005',

'382816',

'388291');

Re: How to get required order in table box

bumin — Thu, 02 Jan 2014 14:37:20 GMT

have you tried with loadorder?

Re: How to get required order in table box

Thu, 02 Jan 2014 14:41:06 GMT

Thanks and I have SQL Server 2008. please helpme how to change this as I am trying this in different ways...

Re: How to get required order in table box

Thu, 02 Jan 2014 14:50:30 GMT

There is no direct equivalent in SQL Server but you could try something like this (just using a few Reference values to simplify the example):

Data:

LOAD

RowNo() as SortOrder,

ID,

Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464')

ORDER BY CASE Reference

WHEN '371284' THEN 1

WHEN '373896' THEN 2

WHEN '374493' THEN 3

WHEN '411464' THEN 4

ELSE 5

END;

It's a pain to type so try it with a few values only first to see if it works. I don't have SQL Server installed so cannot test. If it works then the TableBox can be sorted by field SortOrder.

Re: How to get required order in table box

bumin — Thu, 02 Jan 2014 14:51:04 GMT

try this

DataLoadOrder:

LOAD * Inline [

'373896',

'374493',

'411464',

'413172',

'415033',

...

'423629',

'504005',

'382816',

'388291'

];

DataOrder:

LOAD

ID,

Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464',

'413172',

'415033',

...

'423629',

'504005',

'382816',

'388291');

drop table DataLoadOrder;

Re: How to get required order in table box

Thu, 02 Jan 2014 14:59:55 GMT

Good call Bumin.

Slight tweak (add column header and remove commas):

DataLoadOrder:

LOAD * Inline [

Reference

'373896'

'374493'

'411464'

'413172'

'415033'

...

'423629'

'504005'

'382816'

'388291'

];

DataOrder:

LOAD

ID,

Reference;

SQL SELECT *

FROM "table"

where Reference in ('371284',

'373896',

'374493',

'411464',

'413172',

'415033',

...

'423629',

'504005',

'382816',

'388291');

drop table DataLoadOrder;

Re: How to get required order in table box

bumin — Thu, 02 Jan 2014 15:23:26 GMT

thanks