topic Re: count expression with if statement in QlikView

count expression with if statement

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to get the difference between col 2 and col 3 for table b as shown in the table. I am using the expression as below and not quite successful. Please advice

= if ([Record Type] = 'Replace',(count ({<Source = {'tableb'}> - <[Record Type] = {'Route'},Source = {'tableb'}>} [ID])))

Record Type	table a	table b	table b (col2-col3)
New	2	2	NA
Replace	3	3	0
route	3	3	0
cancel	1	1	NA

Thanks in advance.

Re: count expression with if statement

vishsaggi — Wed, 10 Aug 2016 17:37:23 GMT

What is your expected output should be? Why Record Type 'New' and Cancel has NA ?

Aren't these (table a and table b) your actual column names ? If yes,

Expr:

[table b] - [table a]

Should give you the results ?

What is the issue you are facing here ?

Re: count expression with if statement

Wed, 10 Aug 2016 17:45:00 GMT

Hi Vish,

Thanks for a quick response. I need the difference between route and replace record types for table B. I am new to qlikview, my expression shows no errors but is not giving me a difference as 0 between 2 record types for same table ( table b). Since its just the difference between replace and route for table B , new and cancel has 'NA' - not applicable.

Re: count expression with if statement

vishsaggi — Wed, 10 Aug 2016 18:08:28 GMT

Can you share a sample app please ? And what is tableA here ? Are we using values from Table A? Sorry I quite did not understand what exactly you are trying to achieve?

Re: count expression with if statement

Wed, 10 Aug 2016 18:17:17 GMT

My bad I am not clear on the requirement.Sorry for any confusion. This is the output i want. I cannot share the app due security reasons. I will try to replicate the req and get the application uploaded sometime.

Record Type	table a	table b	table b (Replace-Route)
New	2	2	NA
Replace	3	3	0
route	3	3	0
cancel	1	1	NA

Re: count expression with if statement

vishsaggi — Wed, 10 Aug 2016 20:38:35 GMT

Try these two and let me know ?

Make sure column names do not have spaces if using spaces then enclose them with '[' and this ']' else use as i used below using underscore ?

Dim: RecordType

Expr1: Sum(table_a)

Expr2: Sum(table_b)

Expr3:

= IF(Match(RecordType, 'New','cancel'), 'NA', Above(Sum(table_b),0) - Below(Sum(table_b),0) )

= IF(Match(RecordType, 'New','cancel'), 'NA', Sum({<RecordType = {'Replace, route'} >} table_b) - Sum({<RecordType = {'Replace,route'} >}table_b) )

Expr4: = table_a - table_b

Thanks,
V.

Re: count expression with if statement

Wed, 10 Aug 2016 21:07:05 GMT

Thanks, It working fine...:)

Re: count expression with if statement

vishsaggi — Wed, 10 Aug 2016 21:26:59 GMT

No problem. Glad it help you.