topic Re: set question ? in QlikView

set question ?

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

Hi Folks I have table like this

MINValue	MAXValue	Identifier
0	0.012	0
0.012	0.0347	1
0.0347	0.1096	2
0.1096	0.3034	3
0.3034	0.6301	4
0.6301	1.5115	5
1.5115	3.4873	6
3.4873	12.9616	7
12.9616	101	8

t2:

value	Monthnames
0	Jan-16
0.01	Feb-16
0.05	Mar-16
0.1	Apr-16
0.1	May-16
0.75	Jun-16
0.2	Jul-16
10	Aug-16
10.5	Sep-16
5.5	Nov-16
50	Dec-15
50.5	Nov-15
3.7	Oct-15
0.013	Mar-15
0.01094	Feb-15
1	Jan-15

in my chart dimension is month names and expression is

if (sum(value)>minvalue and sum(value)<maxvalue,,1,0)

here I don't have any association between the 2 tables

Re: set question ?

tresB — Wed, 30 Nov 2016 04:25:08 GMT

What is your expected output?

Re: set question ?

Anonymous — Wed, 30 Nov 2016 04:31:27 GMT

IN ONE CHART

DIMENSION IS

MONTHNAMES

AND EXPRESSION IS

if (sum(value)>minvalue and sum(value)<maxvalue,,1,0)

THIS IS NOT GIVING CORRECT VALUE

Re: set question ?

tresB — Wed, 30 Nov 2016 04:35:58 GMT

qlik777 view777 wrote:

THIS IS NOT GIVING CORRECT VALUE

That I understood; and I could not understand what your CORRECT VALUE is.

Re: set question ?

Anil_Babu_Samineni — Wed, 30 Nov 2016 04:55:29 GMT

May be typo error. I just removed one , from your expression.

if (sum(value)>minvalue and sum(value)<maxvalue,1,0)

Can you confirm me what was the values are minvalue, maxvalue

Re: set question ?

Anonymous — Wed, 30 Nov 2016 05:08:50 GMT

Hi,

You can create associate key from following way:

Script:

Table1:

LOAD * INLINE [

MINValue, MAXValue, Identifier

0, 0.012, 0

0.012, 0.0347, 1

0.0347, 0.1096, 2

0.1096, 0.3034, 3

0.3034, 0.6301, 4

0.6301, 1.5115, 5

1.5115, 3.4873, 6

3.4873, 12.9616, 7

12.9616, 101, 8

];

table3:

LOAD *, RowNo(),MINValue as value1

Resident Table1;

DROP Table Table1;

Table2:

LOAD * INLINE [

value, Monthnames

0, Jan-16

0.01, Feb-16

0.05, Mar-16

0.1, Apr-16

0.1, May-16

0.75, Jun-16

0.2, Jul-16

10, Aug-16

10.5, Sep-16

5.5, Nov-16

50, Dec-15

50.5, Nov-15

3.7, Oct-15

0.013, Mar-15

0.01094, Feb-15

1, Jan-15

];

table4:

load

*,RowNo()

Resident Table2;

DROP Table Table2;

Please find attached QVW for detail information and result table

Re: set question ?

praveenkumar_s — Wed, 30 Nov 2016 05:25:58 GMT

Can anybody tell me how to extract list from powershell

Re: set question ?

jonathandienst — Wed, 30 Nov 2016 05:41:53 GMT

I think you need an interval match - something like this script:

t2:

LOAD

value

MonthNames

FROM ...

;

Left Join(t2)

IntervalMatch(value)

LOAD MINValue, MAXValue - 1e-9 as MAXValue

FROM ... // min - max table

;

Left Join(t2)

LOAD MINValue, MAXValue - 1e-9 as MAXValue, Identifier

FROM ... // min - max table

;

Replace the ellipses with the correct FROM clause for your system.

Re: set question ?

hemanthaanichet — Wed, 30 Nov 2016 05:42:33 GMT

Hi,

I dnt know what is exact output your are in need i guess and solved to some extend may be this is the output are in need off

Attached QVW for your reference

let me know if you have any queries

Regards

Hemanth

Re: set question ?

Anonymous — Wed, 30 Nov 2016 07:59:28 GMT

i want to do this in chart expression level

Re: set question ?

Anonymous — Wed, 30 Nov 2016 08:05:28 GMT

I wanna do it in hart expression level

Re: set question ?

muniyandi — Wed, 30 Nov 2016 08:20:39 GMT

Create two variable minvalue1 , maxvalue1

Variable Expression :

minvalue1 = min(minvalue )

maxvalue1 = max(maxvalue )

Layout condition enable expression :

if (sum(value)>=minvalue1 and sum(value)=< maxvalue1,1,0)

it will work based on your current selection.

Re: set question ?

Anonymous — Thu, 01 Dec 2016 04:01:10 GMT

Hi Folks,

Thanks for ur response

My requirement is

I have 2 tables with no association

t1:

load * inline [

month,value

jan15,0.01

feb15,0.02

mar15,0.03

apr15,0.08

may15,1

jun15,2.5

jul15,10

aug15,20

sep15,35

oct15,100

nov15,100.1

dec15,125

];

in table 2 I have data like this

t2:

load * inline [

minvalue,maxvalue

0.01,0.03

0.04,1

2,25

26,50

51,100

100,105

];

here my re is I wanna creat a chart with table 1 data

dimension is month

and expression is

if(value>=0 and value <=0.03 ,'a',

if(value>=0.04 and value <=0.5 ,'b',

if(value>=0.6 and value <=1 ,'c',

if(value>=2 and value <=100 ,'d',

))))

this works well

but here instead of hardcoding the values in expression level ,

I wannna use min and max columns in this expression level .

where I have no association between the tables

Re: set question ?

jonathandienst — Thu, 01 Dec 2016 05:29:19 GMT

>>i want to do this in chart expression level

I hope your data set is small...

Re: set question ?

Anonymous — Thu, 01 Dec 2016 05:37:22 GMT

Yes

Re: set question ?

svinnakota — Thu, 01 Dec 2016 05:47:56 GMT

Hi Qlik777,

I am having little confusion in understanding the requirement. However,

if (sum(value)>minvalue and sum(value)<maxvalue,,1,0)

If you are considering the max and min values from t1(min(value),max(value)) then you can achieve by using the variable for min and max values and then using set analysis.

What does the indentifier field in the t1 table refers to.

The selection for both the tables can be done when there is a linkage.

Re: set question ?

Anonymous — Thu, 01 Dec 2016 06:04:11 GMT

Hi Santhosh ,

can u refer to my previous reply's ,

I have posted with sample data and my clear requirement

Re: set question ?

nagarjuna_kotha — Thu, 01 Dec 2016 06:06:18 GMT

Hey ,

Try this:

U said , data is not associated. by using this script data wil associate.

T1:

LOAD * INLINE [

MINValue, MAXValue, Identifier

0, 0.012, 0

0.012, 0.0347, 1

0.0347, 0.1096, 2

0.1096, 0.3034, 3

0.3034, 0.6301, 4

0.6301, 1.5115, 5

1.5115, 3.4873, 6

3.4873, 12.9616, 7

12.9616, 101, 8

];

T2:

LOAD * INLINE [

value, Monthnames

0, Jan-16

0.01, Feb-16

0.05, Mar-16

0.1, Apr-16

0.1, May-16

0.75, Jun-16

0.2, Jul-16

10, Aug-16

10.5, Sep-16

5.5, Nov-16

50, Dec-15

50.5, Nov-15

3.7, Oct-15

0.013, Mar-15

0.01094, Feb-15

1, Jan-15

];

Left Join (T1)

IntervalMatch(value)

LOAD

MINValue,

MAXValue

Resident T1;

I hope this might helps you.

-Nagarjun

Re: set question ?

Anonymous — Thu, 01 Dec 2016 06:10:14 GMT

Hi Nagarjuna,

I want do this thing in my chart calculation it self

don't want touch any backend code

Re: set question ?

nagarjuna_kotha — Thu, 01 Dec 2016 06:27:51 GMT

So please share ur expected output , then we ll try .

assuming will not work

-Nagarjun