Count issue with a filter

[shyamcharan]

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.

[vinieme12]

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;

[vinieme12]

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

[shyamcharan]

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.

[vinieme12]

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

[vinieme12 · Accepted solution]

anywaysy you can use below Expression

Initiation count

count(DISTINCT InitiationID)

ResponseCount:

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

TransportCount

sum(TransportCount)

[nitin_01]

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.

[vinieme12]

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;

[nitin_01]

Thanks Vineeth for explanation.

Regards,

Nitin

[vinieme12]

shyamcharan‌

If the above has resolved the problem please close the thread

Qlik Community Tip: Marking Replies as Correct or Helpful