topic Straight table values filtering in QlikView

Straight table values filtering

Thu, 20 Dec 2012 09:36:10 GMT

Hi.

I've got an issue with one table where I want to filter values that are not corresponding to my query. Or more exactly, the current query I make let appear all the values.

Here is the screenshot of the table.

In this table, the first 2 columns are dimensions (customer code and date of delivery)

the 3rd one is based on this expression :

count(DISTINCT {$<[Entete Souche]={'Facturation'}, [Entete Type]={6},[Entete Provenance]={'Avoir'},[Entete Type Avoir]={"GLOBAL"}>} CodeDocument)

and works perfectly

and the 4th is currently :

=count({1<[Entete Souche]={'Facturation'}, [Entete Type]={6},[Entete Provenance]={'Facture'}>} DISTINCT CodeDocument)

This expression is giving me all the deliveries within the database but I want only to get those correspondig to the customers that have an "Avoirs" (column 3 > 0). I've tried different solutions but none work for now.

Any idea ?

Re: Straight table values filtering

Gysbert_Wassenaar — Thu, 20 Dec 2012 10:07:35 GMT

Maybe like this:

=count({1<[Entete Souche]={'Facturation'},

[Entete Type]={6},

[Entete Provenance]={'Facture'},

CodeDocument = {"=$(count(DISTINCT {$<[Entete Souche]={'Facturation'},

[Entete Type]={6},

[Entete Provenance]={'Avoir'},

[Entete Type Avoir]={'GLOBAL'}>} CodeDocument)>0)"}

>} DISTINCT CodeDocument)

Re: Straight table values filtering

Thu, 20 Dec 2012 11:20:51 GMT

The code you add return 0 as code document but I think this is the idea to return the documents codes based on current selection.

Re: Straight table values filtering

Thu, 20 Dec 2012 11:32:50 GMT

Hi,

I think this might help you..

In the query, give an alias to the field you are fetching,

ex)

select name as NAME_TEMP.

Then, in your chart, populate 'NAME_TEMP' instead of name. Note that no other query should have this alias for the same field.

Then, go to Settings->Document Properties->Tables-> check Loosely Coupled for all tables.

Hope this solves your problem.

Regards,

Anju

Re: Straight table values filtering

Thu, 20 Dec 2012 11:53:30 GMT

If I do that I loose all the links among the different tables in the data structure and so the queries in other chart no more work :s

Re: Straight table values filtering

Thu, 20 Dec 2012 11:57:25 GMT

Can you share your app please?

Re: Straight table values filtering

Thu, 20 Dec 2012 11:59:43 GMT

You can check this doc.

Re: Straight table values filtering

Thu, 20 Dec 2012 12:04:32 GMT

note that in my doc,

i have modified the expressions field...in one chart, I populate payingbase_subscriber, in the other I populate payingbase_vod etc...all fetched from independent queries, but my dimension fields have same alias, i.e partner and batchmonth,,and the filters are these two fields...so, on making selections to these fields, since the two filters are common to all queries, the link between tables won't be lost.

Regards,

Anju

Re: Straight table values filtering

Thu, 20 Dec 2012 13:17:02 GMT

Here is the file. I take a look at your example.

The table I'm talking about is on the second tab, down right, called Avoir.

So I make the selection of the weeks on wich I do the control and I verify with this specific table whether I had multiple orders the day of delivery of the original invoice on which there is a credit note placed.

Thank you for your help.

Sebastien

Re: Straight table values filtering

Gysbert_Wassenaar — Thu, 20 Dec 2012 13:26:57 GMT

Maybe we're making this harder than it needs to be. Does this give the result you want:

=if(Avoirs>0, count({1<[Entete Souche]={'Facturation'}, [Entete Type]={6},[Entete Provenance]={'Facture'}>} DISTINCT CodeDocument))

Re: Straight table values filtering

Thu, 20 Dec 2012 13:48:29 GMT

Hehe. That's so simple. You've done it Gysbert. THank you very much. (I fee a bit stupid not to have think of that ^^)