topic Re: Straight table expression not working? in QlikView

Straight table expression not working?

Mon, 18 Jan 2016 15:44:21 GMT

HI,

I have to write a expression based on below condition

IF Status='Open' then NUM(DataColumn1-DataColumn2)

I am trying to use if(Status=Open', NUM(DataColumn1-DataColumn2)). But straight table column is showing blank. table is not loading any data. Is there any error in expression? please can anyone suggest me

Thanks.

Re: Straight table expression not working?

sunny_talwar — Mon, 18 Jan 2016 15:46:21 GMT

May be try this:

=Only({<Status = {'Open'}>} DataColumn1 - DataColumn2)

Re: Straight table expression not working?

Mon, 18 Jan 2016 15:56:46 GMT

Hi,

Try the below one and see also the attached app,Hope it helps!!

=if(Status='Open',num(DataColumn1)-num(DataColumn2))

Re: Straight table expression not working?

Mon, 18 Jan 2016 16:05:11 GMT

Thanks. Tried this. still showing blanks

Re: Straight table expression not working?

Mon, 18 Jan 2016 16:05:20 GMT

Thanks. Tried this. still showing blanks

Re: Straight table expression not working?

Mon, 18 Jan 2016 16:09:20 GMT

Give this a try

=if(Match(Status,'Open'), Num(DataColumn1) - Num(DataColumn2))

Re: Straight table expression not working?

Mon, 18 Jan 2016 16:11:17 GMT

Could you post your sample app?

Re: Straight table expression not working?

Anonymous — Mon, 18 Jan 2016 20:39:17 GMT

which is your dimension?, each category in your dimension can only have one status?

Re: Straight table expression not working?

MarcoWedel — Mon, 18 Jan 2016 20:55:39 GMT

‌missing single quote.

regards

Marco

Re: Straight table expression not working?

Tue, 19 Jan 2016 13:11:48 GMT

dimension is only below one

Week(EnterDate)

Re: Straight table expression not working?

Tue, 19 Jan 2016 13:12:27 GMT

Where am I missing single quote? please let me know

Re: Straight table expression not working?

Anonymous — Tue, 19 Jan 2016 13:15:50 GMT

What about this?

if(Status='Open', NUM(DataColumn1-DataColumn2))

if(Status='Open', (DataColumn1-DataColumn2)).

Re: Straight table expression not working?

Mark_Little — Tue, 19 Jan 2016 13:20:49 GMT

Hi,

I would guess you are getting a number of results returned for each of the field in your expression, Try something like

if(Status='Open', NUM(MAX(DataColumn1)-MAX(DataColumn2)))

to test.

I think Need to make sure that DataColumn1 and DataColumn2 are just returning one distinct value.

Mark

Re: Straight table expression not working?

Tue, 19 Jan 2016 15:08:09 GMT

Thanks. Tried not working.

Re: Straight table expression not working?

Mark_Little — Tue, 19 Jan 2016 15:27:14 GMT

Hi Amelia,

Can you supply some demo data?

Logically everything suggested would work in a standard example.

There could be a number of reasons you seeing blank values return, like String values, multiple values being evaluated when one is expected.

Mark

Re: Straight table expression not working?

Tue, 19 Jan 2016 15:50:41 GMT

here is sample data

Week(EnterDate)	Data1
1	52
2	63
3	107
4	54
5	79
6	80
7	108
8	80
9	78
10	106
11	80

Data1 is calculated by using expression NUM(DataColumn1-DataColumn2) and Dimensions are Dim1: Week(EnterDate) and Dim2: =IF(MATCH(Status,'Open'),Status), Dim3: CustomerID

Week(EnterDate)	Data2
1	18.2
2	17.0
3	48.0
4	30.5
5	20.9
6	29.3
7	42.4
8	30.7
9	14.5
10	17.2
11	34.9

Data2 is calculated by using expression NUM(DataColumn3-DataColumn4) and Dimensions are Dim1: Week(EnterDate) and Dim2: =IF(MATCH(Status,'Closed'),Status), Dim3: CustomerID

Week(EnterDate)	Data3
1	102
2	99
3	102
4	100
5	106
6	91
7	82
8	84
9	75
10	93
11	98

Data3 is calculated by using expression NUM(DataColumn5-DataColumn6) and Dimensions are Dim1: Week(EnterDate) and Dim2: =IF(MATCH(Status,'Required'),Status), Dim3: CustomerID

above all three tables are working fine. but when I am merging data into one straight table blank values are coming. I need a table as below with Dimension: Week(EnterDate) and expressions should be with Data1, Data2, Data3

Week(EnterDate)	Data1	Data2	Data3
1	52	18.2	102
2	63	17.0	99
3	107	48.0	102
4	54	30.5	100
5	79	20.9	106
6	80	29.3	91
7	108	42.4	82
8	80	30.7	84
9	78	14.5	75
10	106	17.2	93
11	80	34.9	98

Please help me to sort out this. sorry if I am not clear in my post when I asked at the beginning

Re: Straight table expression not working?

sunny_talwar — Tue, 19 Jan 2016 17:30:56 GMT

Try this may be:

Dimension:

1) Week(EnterDate)

2) CustomerID

Expressions:

Data1: Num(Only({<Status = {'Open'}>}DataColumn1)-Only({<Status = {'Open'}>}DataColumn2))

Data2: Num(Only({<Status = {'Closed'}>}DataColumn1)-Only({<Status = {'Closed'}>}DataColumn2))

Data3: Num(Only({<Status = {'Required'}>}DataColumn1)-Only({<Status = {'Required'}>}DataColumn2))

Re: Straight table expression not working?

MarcoWedel — Tue, 19 Jan 2016 22:24:01 GMT

maybe just a typo in your example:

if(Status='Open', NUM(DataColumn1-DataColumn2))

regards

Marco

Re: Straight table expression not working?

rwunderlich — Tue, 19 Jan 2016 23:41:38 GMT

Are DataColumn1, DataColumn2, fields in your data? Or labels in your chart?

-Rob

Re: Straight table expression not working?

jagan — Wed, 20 Jan 2016 01:18:56 GMT

Hi,

Try like this using Sum() instead of Only()

Sum({<Status = {'Open'}>}DataColumn1)-Sum({<Status = {'Open'}>}DataColumn2)

Regards,

Jagan.