topic Re: order by TimeBand in QlikView

order by TimeBand

Anonymous — Tue, 14 Nov 2017 10:55:01 GMT

Hi, I have a field called TimeBand that contains the following data:

> 5y
1m
2 and
3m
3y
4y
5y
6m
12m
1d
7d
1m
2m
3m
6m
1y
2 and
3y
4y
5y
+ 5y

As you can see are periods of time. I need to sort them from minor to major but I do not know how to do it. What I want is for it to look like this: (days, months, years)

1d
7d
1m
2m
3m
6m
12m
1y
2 and
3y
4y
5y
> 5y

Thanks

Re: order by TimeBand

Anonymous — Tue, 14 Nov 2017 11:26:47 GMT

Try this in "Sort by Expression":

if(wildmatch(Period,'>*'),4,if(wildmatch(Period,'*and'),3,if(wildmatch(Period,'*d'),1,if(wildmatch(Period,'*m'),2,if(wildmatch(Period,'*y'),3,5)))))

Re: order by TimeBand

Anonymous — Tue, 14 Nov 2017 11:35:07 GMT

Thanks Aron,

the field where I get that data is called 'bucket', do I have to change the word period for bucket?

Re: order by TimeBand

Anonymous — Tue, 14 Nov 2017 11:38:33 GMT

Yes change the the word period for bucket or whatever the field name is.

Re: order by TimeBand

sunny_talwar — Tue, 14 Nov 2017 11:49:06 GMT

Are these bands created in the script? I would suggest using dual function while your create these bands and then you can easily sort on numerically

Re: order by TimeBand

Anonymous — Tue, 14 Nov 2017 11:54:32 GMT

it is a field that comes like this from an excel.

How is that function you are talking about?

thanks

Re: order by TimeBand

sunny_talwar — Tue, 14 Nov 2017 11:58:44 GMT

You can read about Dual here:

How to use- Dual()

Alternatively, you can use Mapping load to assign a numerical value to your field in the script.