topic Re: List dimension based on expression values in QlikView

List dimension based on expression values

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm running into a bit of a snag, because I'm trying to create some dimension values based on expression values. I have a straight table where I've added an expression (labeled as Bucket) which contains the following nested formula:

if(Age<=0,'Current',if(Age>=1 and Age<16,'1 - 15',if(Age>=16 and Age<31,'16 - 30',if(Age>=31 and Age<61,'31 - 60',if(Age>=61 and Age<91,'61 - 90',if(Age>=91 and Age<121,'91 - 120',if(Age>=121 and Age<151,'121 - 150',if(Age>=151 and Age<181,'151 - 180','180 +'))))))))

You're probably wondering why I have a nested formula in my straight table. It's because Age is an expression field within the straight table that is calculated after plugging in a date into an input box. I'm unable to include this date field in the load script due to the date changing based on the data source as of date.

So...back to my question.

How can I create a list table which has a dimension that produces the following values to help me filter my straight table. I'd also like to include an expression field in the list table to calculate the sums of the dimension values.

Current

1 - 15

16 - 30

31 - 60

61 - 90

91 - 120

121 - 150

151 - 180

180 +

Re: List dimension based on expression values

sunny_talwar — Fri, 09 Sep 2016 21:50:13 GMT

You can use the above expression (but with original expression behind Age) and surround it with Aggr() function...

Aggr(

if(Age<=0,'Current',

if(Age>=1 and Age<16,'1 - 15',

if(Age>=16 and Age<31,'16 - 30',

if(Age>=31 and Age<61,'31 - 60',

if(Age>=61 andAge<91,'61 - 90',

if(Age>=91 and Age<121,'91 - 120',

if(Age>=121 and Age<151,'121 - 150',

if(Age>=151 and Age<181,'151 - 180','180 +')))))))), <Dimensions>)

NOTE:

1) Make sure to replace Age with the actual expression

2) Add all the dimension from your straight table which you have used to create this bucket.

Re: List dimension based on expression values

Fri, 09 Sep 2016 22:17:35 GMT

Like this? Plug this calculation in as a calculated dimension?

=Aggr(

if((Aging-[Due Date])<=0,'Current',

if((Aging-[Due Date])>=1 and (Aging-[Due Date])<16,'1 - 15',

if((Aging-[Due Date])>=16 and (Aging-[Due Date])<31,'16 - 30',

if((Aging-[Due Date])>=31 and (Aging-[Due Date])<61,'31 - 60',

if((Aging-[Due Date])>=61 and (Aging-[Due Date])<91,'61 - 90',

if((Aging-[Due Date])>=91 and (Aging-[Due Date])<121,'91 - 120',

if((Aging-[Due Date])>=121 and (Aging-[Due Date])<151,'121 - 150',

if((Aging-[Due Date])>=151 and (Aging-[Due Date])<181,'151 - 180','180 +')))))))),<Dimensions>)

I tried it, but it doesn't work.

If I use [Due Date] as a dimension, my list becomes way too long. I only want the dimension values to be the bucket info.

Re: List dimension based on expression values

sunny_talwar — Fri, 09 Sep 2016 22:49:13 GMT

What is your dimension which you used to create the buckets in the straight table?

Re: List dimension based on expression values

Fri, 09 Sep 2016 22:51:20 GMT

The dimension that I used to create the buckets in the straight table are as follows:

Age (Aging-[Due Date] )

Due Date (included in loan script)

Re: List dimension based on expression values

Fri, 09 Sep 2016 22:51:50 GMT

Re: List dimension based on expression values

effinty2112 — Sat, 10 Sep 2016 11:33:31 GMT

Hi Vic,

You could consider using IntervalMatch to create age groups.

[Age Groups]:

LOAD * INLINE [

Age Group, From, To

1 - 15, 1, 15

16 - 30, 16, 30

31 - 60, 31, 60

61 - 90, 61, 90

91 - 120, 91, 120

121 - 150, 121, 150

151 - 180, 151, 179

180 +, 180, 9999

];

Data: //some dummy data

LOAD

Ceil(Rand()*300) as Age,

Round(Rand()*1000) as Measure

AutoGenerate 10000;

IntervalMatch:

IntervalMatch(Age) LOAD From, To Resident [Age Groups];

Result:

Age Group	Sum(Measure)
	4983248
1 - 15	253882
16 - 30	256209
31 - 60	502305
61 - 90	470784
91 - 120	511971
121 - 150	503987
151 - 180	495795
180 +	1988315

Hope this helps

Cheers

Andrew

Re: List dimension based on expression values

Mon, 12 Sep 2016 17:39:52 GMT

I would do this, however the age is not determined until after the load has been completed. The age field is determined by a date that I enter in after the load script is done. If there were a way that I could include in the load script to create a field "As of date" which was determined by the 2nd half of the source date file name, then this I think would be the best solution.

Is there any way to do this? Create a date field during the load script which is determined by the source file name?

Re: List dimension based on expression values

marcus_sommer — Mon, 12 Sep 2016 18:58:21 GMT

If you used equally buckets you could try it with an expression like:

pick(ceil((Aging-[Due Date]) / 30) + 1, 'Current', '1 - 30', '31 - 60', …)

Even if this isn't suitable for you it could be better (especially regarding to possible performance issues) to use a pick(match()) combination instead of nested if-loops and/or aggr-functions.

- Marcus

Re: List dimension based on expression values

effinty2112 — Tue, 13 Sep 2016 08:49:30 GMT

Hi Vic,

If your input date is held in a variable vInput then try this calculated dimension:

=Pick(Rangemin(13,Ceil(([Due Date]-'$(vInput)')/15)),

'1-15',

'16-30',

'31-60',

'61-90',

'91-120',

'121-150',

'151-180',

'180+')

Sort it by Min([Due Date]) descending.

Re: List dimension based on expression values

chetansehgal — Tue, 13 Sep 2016 11:05:37 GMT

By source file name means the file in which you are doing all this stuff or it is a filename of another qvw file?

Re: List dimension based on expression values

Mon, 19 Sep 2016 15:06:17 GMT

By source file name, I meant the file which is used in the load script, the FROM file. If I could add a date into the filename which is used in the load script to determine the "as of date" of the data.