topic Re: RE : SORT ORDER in QlikView

RE : SORT ORDER

Thu, 21 Aug 2014 12:38:02 GMT

Hi All,

I have a field which is a combination of dates(ie..20130812,20121001,20121003). the requirement is i need to make separate fields from this field in the descending order of dates.

EG.

.field-1 as 20130812

filed-2 as 20121003

filed-3 as 20121001

like this...

how can it be achieved??

please suggest..

thanks in advance../

Re: RE : SORT ORDER

ecolomer — Thu, 21 Aug 2014 12:40:50 GMT

can you explain more, please

the fields are in a record?

Re: RE : SORT ORDER

ecolomer — Thu, 21 Aug 2014 12:42:49 GMT

Separate fields in year, month and day?

left(field, 4) as year,

mid(field, 5,2) as month,

rigth(field, 2) as day

Re: RE : SORT ORDER

ashfaq_haseeb — Thu, 21 Aug 2014 12:45:59 GMT

Hi,,

try below

load *,SubField(Test,',') as Test1;

load * Inline

[

Test

"20130812,20121001,20121003"

]

Regards

ASHFAQ

Re: RE : SORT ORDER

MayilVahanan — Thu, 21 Aug 2014 12:47:10 GMT

Try like this

I think your date is in text format..

Date(Date#(DateField, 'YYYYMMDD')) AS Date

After that display as in descending order by using Sorting tab.

Edit:

If you need to separate the field values ., try with SubField() functions.

Ex:

SubField(DateField,',')

Re: RE : SORT ORDER

Thu, 21 Aug 2014 12:55:14 GMT

Hi..

The Main field contains the values like ie..20130812,20121001,20121003 (This is 1 value of the filed like this there are 'n' no of records) these are all dates in YYYYMMDD format.it doesn't have any order as ascending or descending.

Requirement:

need to make 3 Sub fields according to the descending order of dates

here for this value it will be

Subfield 1 : 20130812 (max date)

Subfield 2 : 20121003(max-1 date)

Subfield 3 : 20121001 (Max-2 date)

Re: RE : SORT ORDER

ashfaq_haseeb — Thu, 21 Aug 2014 12:59:11 GMT

Hi,

did you try my suggestion.

Can you post sample data with 5-10 records with expected output.

Regards

ASHFAQ

Re: RE : SORT ORDER

ashfaq_haseeb — Thu, 21 Aug 2014 13:02:33 GMT

Or you need something like this.

load *,SubField(Test,',') as Test1,

SubField(Test,',',1) as Test2,

SubField(Test,',',2) as Test3,

SubField(Test,',',3) as Test4;

load * Inline

[

Test

"20130812,20121001,20121003"

]

Regards

ASHFAQ

Re: RE : SORT ORDER

Thu, 21 Aug 2014 13:04:18 GMT

hi..

Thanks for the reply..

as you said we can separate the fields using subfield and date functions can be applied.

But i need to make the subfield according to the descending order of dates.

How it will be possible??

pls suggest...

Thanks

Reena

Re: RE : SORT ORDER

MayilVahanan — Thu, 21 Aug 2014 13:12:50 GMT

I think, need to use macro in script to rearrange the dates before use the subfield function.

Re: RE : SORT ORDER

rbecher — Thu, 21 Aug 2014 13:30:38 GMT

Hi Reena,

this works for 3 date values:

LOAD *,

NumMax(Date1,Date2,Date3) as DateSorted1,

(Date1 + Date2 + Date3 - NumMax(Date1,Date2,Date3) - NumMin(Date1,Date2,Date3)) as DateSorted2,

NumMin(Date1,Date2,Date3) as DateSorted3;

LOAD RawData,

SubField(RawData,',',1) as Date1,

SubField(RawData,',',2) as Date2,

SubField(RawData,',',3) as Date3

Inline

[

RawData

"20130927,20121010,20140311"

"20140502,20140821,20131230"

];

- Ralf

Re: RE : SORT ORDER

Fri, 22 Aug 2014 04:23:59 GMT

Hi...

Thanks for the reply,...

i hope ,in case if i have more no of dates in the field then i cant use this method ??

But in my case,it may not be exactly 3 dates,it can be more or can vary.. .:(

Re: RE : SORT ORDER

rbecher — Fri, 22 Aug 2014 07:50:14 GMT

No this works only for 3 data values in a field. If you have mutable amount of date values in the field you can use an aggregation to get out the sorted first 3 values:

LOAD ID,

FirstSortedValue(Date,-Date,1) as Date1,

FirstSortedValue(Date,-Date,2) as Date2,

FirstSortedValue(Date,-Date,3) as Date3

Group By ID;

LOAD ID,

SubField(RawData,',') as Date

Inline

[

ID,RawData

1,"20130927,20121010,20140311"

2,"20140502,20140821,20131230,20140109"

3,"20130717,20121112,20140209,20130903,20140423"

4,"20130301,20140601,20131230"

];

- Ralf