topic qlikview syntax in QlikView

qlikview syntax

Wed, 27 Apr 2011 13:27:21 GMT

i want to write this quary in qlikview syntax as an expretion.

i want to count all "Mezahhe_Mismach" that have 5 but not 9 (just 5).

Mezahhe_Mismach is my key but its the same key for kod 5 and kod 9

select * from VW_FACT_WINS t

where not exists (

select * from VW_FACT_WINS w

where t.Mezahhe_Mismach=w.Mezahhe_Mismach

and w.i_end_function_code='9')

thanks

qlikview syntax

Miguel_Angel_Baeyens — Wed, 27 Apr 2011 13:30:53 GMT

Hello,

It should look like

Count({< i_end_function_code = {5} >} Mezahhe_Mismach)

Hope that helps.

qlikview syntax

chris_johnson — Wed, 27 Apr 2011 13:39:36 GMT

Hi,

I think the easiest solution is to load all of your data into QlikView and then to filter out the information you don't want into another table then drop the first table. Something like:

TableA:

select * from VW_FACT_WINS;

TableB:

noconcatenate load *

resident TableA

where i_end_function_code <> 9;

drop table TableA;

Regards,

Chris

qlikview syntax

chris_johnson — Wed, 27 Apr 2011 13:40:35 GMT

Ah, sorry. I've just noticed that you want it as an expression not in the script.

Ignore me!

qlikview syntax

Wed, 27 Apr 2011 13:41:37 GMT

i'll try to explain better:

kod 5 - kod for open

kod 9 - for close

i need to count only those that open now . if i use your kod ,i'll get all Mezahhe_Mismach that i have in the db with kod 5.

i need thos with kod 5 that dont have kod 9 .

i tried this:

=count({$<FACT_WINS_i_end_function_code={5},FACT_WINS_i_end_function_code<>{9}>}distinct FACT_WINS_Mezahhe_Mismach)

but its not good

thanks

qlikview syntax

Wed, 27 Apr 2011 13:57:50 GMT

Try :

count({$<FACT_WINS_i_end_function_code={5},FACT_WINS_i_end_function_code -= {9}>}distinct FACT_WINS_Mezahhe_Mismach)

Note the -= as opposed to <>

Thanks

qlikview syntax

Miguel_Angel_Baeyens — Wed, 27 Apr 2011 13:59:49 GMT

Hi,

Again, if you only want your chart to display those that FACT_WINS_i_end_function_code equals to 5, use

Count({< FACT_WINS_i_end_function_code = {5} >} FACT_WINS_Mezahhe_Mismach)

That should return only for code 5, and not 9.

Or is that any code 5 may have as well code 9 and you only want those that have code 5 but not code 9?

Count({< FACT_WINS_i_end_function_code = {5} > - < FACT_WINS_i_end_function_code = {9} >} FACT_WINS_Mezahhe_Mismach)

Hope that helps.