topic Re: Count issue with a filter in QlikView

Count issue with a filter

shyamcharan — Tue, 13 Dec 2016 02:44:52 GMT

Hi Experts,

I have a scenario as show below

MasterID	InitiationID	ResponseID	TransportID	InitiationRowID	Destination	ResponseCount	TransportCount	InitiationCount
M1	I1	A1		1		1	0	1
M2	I1	A2	R1	2	Dest1	1	1

In the above data:

responses = 2

Transports = 1

Initiations = 1

For 2 responses out of which one was transported and only one reached the destination Dest1, there is one Initiation associated with it.

So my requirement is when I select a 'Dest1' in the filters, I want the result as :

responses = 2

Transports = 1

Initiations = 1

This is because the user want to see Initiations as 1, though only one response has a transport with a destination.

Also, I have to use the InitiationCount.

However, I get as below(with Dest1 as filter):

responses = 1

Transports = 1

Initiations = 0

Please find the attached sample QVW.

Thanks in advance.

Regards,

Shyam.

Re: Count issue with a filter

vinieme12 — Tue, 13 Dec 2016 05:40:07 GMT

Populate the Missing Destination for Common Initiation ID's as below

TEMP:

LOAD * INLINE [

MasterID,InitiationID,ResponseID,TransportID,InitiationRowID,Destination,ResponseCount,TransportCount,InitiationCount

M1,I1,A1,,1,,1,0,1

M2,I1,A2,R1,2,Dest1,1,1

];

NoConcatenate

RESPONSE:

LOAD

MasterID,

InitiationID,

ResponseID,

TransportID,

InitiationRowID,

Destination,

If(len(Destination)<1,Previous(Destination),Destination) as UpdatedDestination,

ResponseCount,

TransportCount,

InitiationCount

Resident TEMP

Order by InitiationID,Destination DESC;

DROP TABLE TEMP;

Re: Count issue with a filter

vinieme12 — Tue, 13 Dec 2016 06:19:05 GMT

if this works for you, please mark the response as correct and close the thread

Re: Count issue with a filter

shyamcharan — Tue, 13 Dec 2016 06:35:27 GMT

Hi Vineeth,

Thanks heaps. That's a wonderful fix.

Your solution works perfectly for my requirement.

However, would you be able to suggest what should I do on the Data Model to get this scenario fixed?

Also, in my data there could be a scenario as below:

Initiation count = 1

Response count = 2

Transport count = 2

Destination to two different destinations.

As in example in the attached QVW, When I select a destination Dest2 or Dest3, will i be able to get a result like below?

Initiation count = 1

Response count = 2

Transport count = 1

Destination Name = Dest2

This will help me immensely..Thanks in advance.

Regards,

Shyam.

Re: Count issue with a filter

vinieme12 — Tue, 13 Dec 2016 06:54:07 GMT

There is only 1 row for Dest2 so how is it that response is 2?

Re: Count issue with a filter

vinieme12 — Tue, 13 Dec 2016 06:57:02 GMT

anywaysy you can use below Expression

Initiation count

count(DISTINCT InitiationID)

ResponseCount:

sum({<InitiationID = p(InitiationID) ,UpdatedDestination>}ResponseCount)

TransportCount

sum(TransportCount)

Re: Count issue with a filter

nitin_01 — Tue, 13 Dec 2016 07:00:56 GMT

Hi Vineeth,

Appreciated your solution.

But I guess 'order by' clause doesn't work with alphanumeric and text field values. So I just wonder , How ! it actually worked in this case?. If data is small that's why, if so then what in case of larger data set?

Regards,

Nitin.

Re: Count issue with a filter

vinieme12 — Tue, 13 Dec 2016 07:06:17 GMT

in this case it sorts alphabets first, then numbers, then null values

Try it yourself

TEMP:

Load

Pick(Ceil(6*Rand()),'a','b','c','d','e','f') & Ceil(6*Rand()) as Dim

Autogenerate 36;

noconcatenate

Sorted:

Load Dim as Dim2 RESIDENT TEMP

Order by Dim;

Re: Count issue with a filter

nitin_01 — Tue, 13 Dec 2016 07:52:08 GMT

Thanks Vineeth for explanation.

Regards,

Nitin

Re: Count issue with a filter

vinieme12 — Tue, 13 Dec 2016 10:14:36 GMT

shyamcharan ‌

If the above has resolved the problem please close the thread

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Count issue with a filter

shyamcharan — Wed, 14 Dec 2016 01:52:32 GMT

Hi Vineeth,

Sorry for the delayed response.

I really appreciate your replies. Great work.

Thanks heaps.

I am trying to resolve this at the Data Model level. Currently the Incident count is associated with MasterID but not with InitiationID.

I am thinking to change the DM design to have tables as in the attached QVW.

Please suggest if I did anything wrong.

Thanks in advance.

Regards,

Shyam.

Re: Count issue with a filter

vinieme12 — Wed, 14 Dec 2016 07:51:26 GMT

I will check and get back to you

Re: Count issue with a filter

shyamcharan — Thu, 15 Dec 2016 02:28:37 GMT

Thanks Vineeth

Re: Count issue with a filter

vinieme12 — Thu, 15 Dec 2016 10:20:04 GMT

All of your Relational tables are Many to One or ONe to ONe

You can consolidate all of it in one single Master table no need to keep them in separate tables

Master

Left Join(Master)

Response

LeftJOin(Master)

Transport

Left Join(Master)

Bridgetable

Left JOin (Master)

Initiation