topic Re: Match function issue in QlikView

Match function issue

raju_salmon — Tue, 02 Feb 2016 11:29:34 GMT

Hi All,

Please find the attached QVW.

I have created "Expanded Sat and Sun Count YearWise" Chart, it should show the count of sat and sun for the each year.

Ex: 2004 - 52+52=104

If i see expanded chart, it is showing 52 Sat and 52 Sun When we collapsed it, it is showing 366.

Because it is also calculating NULL values, which i have suppressed.

Can we exclude NULL values (262) from calculation?

I have created WeekStatus Field using Match Function. Is there any function which i can use here to do not calculate other than SAT and SUN? Please see the script for this field.

I have lot of requirement like this to use Match function to select few values from main field to derive new field, and i need to use those fields in PIVOT table... due to NULL values, i am not getting correct figure while collapsing the pivot table.

Thanks,

Raju

Re: Match function issue

swuehl — Tue, 02 Feb 2016 11:35:32 GMT

Try an expression

=Count({<WeekStatus = {"*"}>}Date)

Then you can also disable 'Suppress when values is NULL' on dimension tab

Re: Match function issue

swuehl — Tue, 02 Feb 2016 11:37:10 GMT

Or even more simple:

=Count(WeekStatus)

Re: Match function issue

lironbaram — Tue, 02 Feb 2016 11:37:32 GMT

have a look at the attach example

Re: Match function issue

raju_salmon — Tue, 02 Feb 2016 11:42:22 GMT

Thank you for the reply.

I have lot of complex set analysis expression for other requirements, so i would to do these filters at script level and using those new fields as dimensions of pivot tables.

So, any suggestions at script level?

Attached one is just an example i have provided.

Thanks.

Re: Match function issue

raju_salmon — Tue, 02 Feb 2016 11:42:52 GMT

Thank you for the reply.

I have lot of complex set analysis expression for other requirements, so i would to do these filters at script level and using those new fields as dimensions of pivot tables.

So, any suggestions at script level?

Attached one is just an example i have provided.

Thanks.

Re: Match function issue

swuehl — Tue, 02 Feb 2016 11:51:48 GMT

Not sure if I understand, I think you just need to count the correct field:

=Count( WeekStatus)

Re: Match function issue

raju_salmon — Tue, 02 Feb 2016 12:01:36 GMT

I have provided an example Swuehl since i have confidential data.

I need to create a pivot table with few dimensions... and many measures with set analysis.

and those dimension i need to derive (since 10-20 selected values i need to keep, so using match function).

Hence i derived those fields at script level using match function. However for remaining values it is considering as NULL at PIVOT table.

That is the issue. To understand this, i made sample QVW with Calendar.

Re: Match function issue

swuehl — Tue, 02 Feb 2016 12:08:01 GMT

And can you point me to a scenario using your sample or an updated sample where the two solutions I've suggested won't work?

Re: Match function issue

raju_salmon — Wed, 03 Feb 2016 04:05:47 GMT

Your Solution works fine Swuehl when my KPI is related to dimension.

My KPI calculations are completely different than dimension... i am using these dimension to just do filters. So, i am looking for script level filters.

Only one pivot table with 20+ kpi has this requirement, hence not using where clause in the script.

I will try anyway.

Thanks.

Re: Match function issue

raju_salmon — Wed, 03 Feb 2016 04:56:18 GMT

Hi Swuehl,

Can we use only function for set analysis if there is no aggregate function we are unsing?

For exmaple, i am taking one field name as my expression?

So, can i use only ( {SET EXPRESSION} FIELD)????

Re: Match function issue

swuehl — Wed, 03 Feb 2016 10:47:52 GMT

You can use the only() function and use set analysis, since only() is also an aggregation function.

Note that Only() will only return a value if there is one distinct value possible as result when iterated over the possible record set.

The Only Function