qlikview syntax

[#1]

hi

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

[Miguel_Angel_Baeyens]

Hello,

It should look like

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

Hope that helps.

[chris_johnson]

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

[chris_johnson]

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

Ignore me!

[#5]

hi

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

[#6]

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

[Miguel_Angel_Baeyens]

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.