Ignore a complete table with set analysis

[GuillaumeRUE]

Hello,

I'm trying to calculate the number of delevries inside a QV app but I have a little problem.

Here are my tables in my QV app :

So, I would like to calculate the number of deliveries without using tables 'Anomalies', 'Type' and 'Raison'.

In SQL I would do it like this :

SELECT count(*) as Nb_deliveries
FROM Livraison
WHERE status = 5
AND mode= 'T'
AND date_livraison >= some_date
AND date_livraison < some_other_date

But in QV I only get records where there is a link between the delivery and table 'Anomalie'.

I tried using set analysis but I can't get better than a count of the entire delivery table :

=count({1<STATUS={5},MODE={`T`}>}Date_livraison)

So I imagine if I could set the date in the expression it's ok but I don't find how to do it.

If someone can help me that would be perfect 🙂

Guillaume

[agigliotti]

maybe this:

=count(
{1 < STATUS = {'5'}, MODE={'T'},
Date_livraison = {">=some_date<=some_other_date"} >}
Id )

i hope it can help.

[anushree1]

what is your key column connecting livarison and anamolie 

[GuillaumeRUE]

The key is Id for table 'Livraison' and 'Id_livraison' for the table 'Anomalie'

(In my QV App they have the same name obviously)

[GuillaumeRUE]

Ok and if I want the date to be dynamic?

For example if I select a period in my calendar table (ex : from 01/06/2019 to 15/06/2019), is that possible to change some-date and some_other_date?

Maybe something like : 

=count(
{1 < STATUS = {'5'}, MODE={'T'},
Date_livraison = {">=min(date)<=max(date)"} >}
Id )

[anushree1]

The two tables cannot be linked together in qlikview if they do not have atleast one field with the same name so ID  and Id_livraison cant be linking the two tables together can you please chk the Table viewer (Ctrl +T) and then see what is defining key field which is common between the two tables or share the script

[agigliotti]

you can do it as below:

=count(
{1 < STATUS = {'5'}, MODE={'T'},
Date_livraison = {">=$(=min(date))<=$(=max(date))"} >}
Id )

[GuillaumeRUE]

I think I badly explained that part. My schema with the tables was just here to  facilitate comprehesion. Here is the real one :

So here :

Livraison = LDT

Calendrier = CALENDAR_ANOMALIE

Anomalie = ANOMALIES

[anushree1]

are you trying to count Numero Shipment

[GuillaumeRUE]

Result is always 0, with or without selection.

Here is the code with real attributes names : 

=count({1 < LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=$(=min(date))<=$(=max(date))"} >}NUMERO_SHIPMENT )