count expression with if statement

[#1]

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.

[vishsaggi]

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 ?

[#3]

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.

[vishsaggi]

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?

[#5]

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)	Table A - Table B
New	2	2	NA	0
Replace	3	3	0	0
route	3	3	0	0
cancel	1	1	NA	0

[vishsaggi · Accepted solution]

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) )

OR

        = 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.

[#7]

Thanks, It working fine...:)

[vishsaggi]

No problem. Glad it help you.