topic Ignore a complete table with set analysis in QlikView

Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 09:37:50 GMT

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

Re: Ignore a complete table with set analysis

agigliotti — Wed, 19 Jun 2019 09:43:19 GMT

maybe this:

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

i hope it can help.

Re: Ignore a complete table with set analysis

anushree1 — Wed, 19 Jun 2019 09:43:26 GMT

what is your key column connecting livarison and anamolie

Re: Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 09:46:05 GMT

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

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

Re: Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 09:49:07 GMT

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 )

Re: Ignore a complete table with set analysis

anushree1 — Wed, 19 Jun 2019 09:59:55 GMT

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

Re: Ignore a complete table with set analysis

agigliotti — Wed, 19 Jun 2019 10:06:21 GMT

you can do it as below:

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

Re: Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 10:06:42 GMT

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

Re: Ignore a complete table with set analysis

anushree1 — Wed, 19 Jun 2019 10:12:01 GMT

are you trying to count Numero Shipment

Re: Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 10:15:58 GMT

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 )

Re: Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 10:16:33 GMT

Yes, I'm trying this right now :

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

Re: Ignore a complete table with set analysis

agigliotti — Wed, 19 Jun 2019 10:19:17 GMT

you have to remove 1 set operarator like below:

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

1 operator means you take all data regardless any selection made.

Re: Ignore a complete table with set analysis

anushree1 — Wed, 19 Jun 2019 10:20:43 GMT

So can you please try loading Numero Shipment column again like below

LDT:

LOad

Field1, field2,....

Numero Shipment as Test

and then use the new Test Field which is not linked to any table in the expression like:

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

Re: Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 11:11:39 GMT

I copied your code but I still have 0 as result.

When i just write =count( {< LDSTAT = {'5'}, LDMODE={'T'} >} NUMERO_SHIPMENT ) I get the total result so it's fine but as soon as I tried to add restriction on the date I have null result

Re: Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 11:12:47 GMT

I just tried this solution, LDT was not linked to any other table but I get 0 as result

Re: Ignore a complete table with set analysis

sunny_talwar — Wed, 19 Jun 2019 11:21:40 GMT

What is the format for SDDATE field? Also, how is Date linked to SDDATE? In the expression above you use date instead of Date (as seen from your date model)... is that just a typo? Qlik is case sensitive so make sure to use the correct casing.

Re: Ignore a complete table with set analysis

agigliotti — Wed, 19 Jun 2019 11:24:19 GMT

let's try replacing real value to variable ex.

=count( {< LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=01/06/2019<=15/06/2019"} >} NUMERO_SHIPMENT )

to see what you get as result.

Re: Ignore a complete table with set analysis

GuillaumeRUE — Wed, 19 Jun 2019 11:41:27 GMT

@sunny_talwar wrote:
What is the format for SDDATE field? Also, how is Date linked to SDDATE? In the expression above you use date instead of Date (as seen from your date model)... is that just a typo? Qlik is case sensitive so make sure to use the correct casing.

Thank you you pointed my mistakes!

In fact, SDDATE was not in the same format as Date AND I wrote 'date' instead of 'Date'.

Then, I tried :

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

but result was still false, so I added '1' and I finally got something but the number was too big. The reason was QV used all 'NUMERO_SHIPMENT' so I just added a DISTINCT and now I have the good result. So my final expression is :

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

Thank you very much @sunny_talwar @agigliotti and @anushree1 for your help.

Guillaume

Re: Ignore a complete table with set analysis

sunny_talwar — Wed, 19 Jun 2019 11:44:41 GMT

Awesome, I am glad you were able to figure this out.