topic Re: Showing rows only with multiple events in QlikView

Showing rows only with multiple events

farheenayesha — Tue, 05 Dec 2017 14:03:11 GMT

Hi,

In a pivot able, i want to show event dates which has multiple events as below. In the below image, event date 01/03/2016,01/04/2016 and 01/05/2017 has multiple curriculum names and speaker names, hence i want to display only these dates.

Dates like 01/02/2016 and 01/06/2017 are having single curriculum name and speaker name, hence i want to eliminate these rows.

Kindly help.

In the below image, i want only rows highlighted in green color and remove which are highlighted in red. Please find the excel and QVW attached.

Re: Showing rows only with multiple events

sunny_talwar — Tue, 05 Dec 2017 14:07:28 GMT

May be use a straight table to do this

Dimensions

All your fields

Expression

=If(Count(TOTAL <[Event Date]> [Event Date]) > 2, 1)

Hide the expression on the presentation tab

Re: Showing rows only with multiple events

farheenayesha — Tue, 05 Dec 2017 14:37:24 GMT

Thank you for quick reply. Could you please help me to get the result at dimension level. please find the updated excel file attached. and also using this below expression in my pivot table.please consider this expression and please provide the solution at dimension level.

=if(len(trim([Date Trained])) = 0, COUNT({<[Event Status]-={'DROPPED','HOLD','CANCEL'},[Event Type]={'SPKRPGM'},[Site Participant Attendance Status]={'ACCEPTED','ATTENDED'}>}[Vendor Event ID]))

Re: Showing rows only with multiple events

sunny_talwar — Tue, 05 Dec 2017 14:42:54 GMT

Your sample data doesn't even have some of the fields from your expression.... leaving that part aside, may be you can try this as your calculated dimension

=Aggr(If(Count([Event Date]) > 2, [Event Date]), [Event Date])

Make sure to check 'Suppress When Value Is Null' on the dimension tab of the chart properties

Re: Showing rows only with multiple events

farheenayesha — Tue, 05 Dec 2017 15:06:34 GMT

Thanks Sunny.

Actually your expression is working fine without the below expression. However when i use the below expression i am not getting the desired result.

=if(len(trim([Date Trained])) = 0, COUNT({<[Event Status]-={'DROPPED','HOLD','CANCEL'},[Event Type]={'SPKRPGM'},[Site Participant Attendance Status]={'ACCEPTED','ATTENDED'}>} [Event ID]))

Please let me know if any of the above parameters are affecting it

Re: Showing rows only with multiple events

sunny_talwar — Tue, 05 Dec 2017 15:38:09 GMT

May be you need to add the set analysis when you count in your aggr dimension

=Aggr(If(Count({<[Event Status] -= {'DROPPED','HOLD','CANCEL'}, [Event Type] = {'SPKRPGM'}, [Site Participant Attendance Status] = {'ACCEPTED','ATTENDED'}>} [Event Date]) > 2, [Event Date]), [Event Date])

Re: Showing rows only with multiple events

Anonymous — Tue, 05 Dec 2017 15:53:35 GMT

Hi All,

It may be help u ..

Re: Showing rows only with multiple events

Anonymous — Tue, 05 Dec 2017 16:04:06 GMT

Sorry Wrong Attachment

Re: Showing rows only with multiple events

Anonymous — Tue, 05 Dec 2017 16:07:10 GMT

Sorry Again..

Re: Showing rows only with multiple events

Anonymous — Tue, 05 Dec 2017 16:08:15 GMT

Its My Bad

Re: Showing rows only with multiple events

farheenayesha — Thu, 07 Dec 2017 14:15:57 GMT

Thanks Sunny.

However I didn't get the solution yet. I am attaching new Mockup data and Sample file. Please help me to achieve it. I want only records which has multiple event start date where 'date trained' is null.

in the below snapshot I have only one record with date trained is null and I want to remove this record from the table.

I have to achieve this in front end level.

Please let me know if you want more clarification.

afd‌

Re: Showing rows only with multiple events

sunny_talwar — Thu, 07 Dec 2017 14:25:21 GMT

Which table are you looking to remove it from? Speakers Pending Training table?

Re: Showing rows only with multiple events

farheenayesha — Thu, 07 Dec 2017 14:28:44 GMT

Yes Sunny. I am trying to remove all the single records where date trained is null.

Re: Showing rows only with multiple events

sunny_talwar — Thu, 07 Dec 2017 14:36:58 GMT

Is this based on Event Start Date? What will you want to see when 1/6/2016 is selected?

Both the rows because 1/6/2016 have more then one Event ID?

Re: Showing rows only with multiple events

farheenayesha — Thu, 07 Dec 2017 14:48:47 GMT

yes.

Logic : I want to display records which has multiple curriculum id's for one Event start date and also date trained should be null.

When I select 1/6/2016, I should get only two records where date trained is null, as same as your snapshot. However for the date 1/4/2016 , I want to remove the record which is appearing in the Speaker Pending Training Table. Because it has only one record with date trained as null.

Re: Showing rows only with multiple events

sunny_talwar — Thu, 07 Dec 2017 14:58:02 GMT

Try this as a calculated dimension

=Aggr(If(Count(TOTAL <[Event Start Date]> DISTINCT {<[Event Status]-={'DROPPED','HOLD','CANCEL'},[Event Type]={'SPKRPGM'},[Site Participant Attendance Status]={'ACCEPTED','ATTENDED'}>}If(Len(Trim([Date Trained])) = 0, [Vendor Event ID])) > 1, [Vendor Event ID]), [Event Start Date], [Vendor Event ID])

Re: Showing rows only with multiple events

farheenayesha — Thu, 07 Dec 2017 15:31:36 GMT

You are Genius Thank you so much. I am very grateful to you!!

Re: Showing rows only with multiple events

farheenayesha — Fri, 08 Dec 2017 07:40:23 GMT

Hi Sunny,

Just wanted one more scenario. As I had mentioned that I want to show Event start date with Multiple Curriculum ID's and date trained is null. I want more condition to be added. Along with multiple curriculum id's I want to show event start date with multiple site participant SFDC ID as well. In the below image, event dates with multiple site participant SFDC ID is not showing up in the actual table(Second Image).

There is one issue in the solution what you have shared with me. As I want Site Participant SFDC ID as only Attended accepted, However in the current solution, I am seeing the records with Declined and Do Not Show as well. It would be great if you could help me to resolve these two issues.

Re: Showing rows only with multiple events

farheenayesha — Fri, 08 Dec 2017 07:42:08 GMT

Thank you so much for your response

Re: Showing rows only with multiple events

sunny_talwar — Fri, 08 Dec 2017 12:17:38 GMT

I did not see any reference of SFDC ID... what exactly is SFDC ID? And just to be clear, you have two issues above....1) The previous expression isn't working exactly how you would wish, 2) You want to add another condition, correct?