topic Re: P() set analysis for the discussed example in QlikView

P() set analysis for the discussed example

niharika1234 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

This was my original Expression for a Straight Table. This is the expression of Scheduled Task

COUNT({<PRIORITY ={'Tier I'}>DISTINCT TASK_ID)

Now I want to enter the value of Calendar as P() in set analysis because This calendar is independent and I want to put all value in set analysis.

: The value of YEAR = Year, Quarter = Quarter and Month = Year month.

so my query I wrote is below that shows error. Please suggest.

COUNT({<PRIORITY ={'Tier I'}>,<DUE_DATE = P(<YEAR = Year>, <Month = YearMonth>, <Quarter = Quarter>)}DISTINCT TASK_ID)

Re: P() set analysis for the discussed example

sunny_talwar — Tue, 06 Nov 2018 12:06:03 GMT

Would you be able to share a sample to see what you have?

Re: P() set analysis for the discussed example

swuehl — Tue, 06 Nov 2018 12:16:17 GMT

I think you just need to assign the field values for a year and month combination from your data island calendar to an appropriate field associated with DUE_DATE.

Is there such a field with the same format as used in your data island calendar?

I am a bit sceptical looking at Month vs. YearMonth field, at least this seems to be sloppy naming convention.

Then it looks like this

COUNT( {<PRIORITY ={'Tier I'}, Month = p(YearMonth) >} DISTINCT TASK_ID)

Where Month is in fact a field with granularity year & month connected to your DUE_DATE / TASK_ID and showing compatible values to your island calendar YearMonth.

edit:

Modifying your Month field selection this way may need clearing all fields that may show incompatible selections in other calendar fields connected to Month, like Year, pure Month, Quarter etc. To clear a field use the pure field name in your set modifier or maybe just ignore all user selections using set identifier 1.

Re: P() set analysis for the discussed example

niharika1234 — Tue, 06 Nov 2018 16:21:53 GMT

Hello,

Thanks for all your feedback. Initially this Dashboard was developed and I am going to add a new table with huge data . I am not able to Join as Previous join was Cross Join and when I join it hangs.

So, I am trying to make Task as data island but user wants when the Click the calendar previously made should update values. Say one field is Scheduled Task, the expression is COUNT({<PRIORITY ={'Tier I'}>DISTINCT TASK_ID). My apology Stefan, The Year field is Year, Quarter field is Qurter and Month is Monthname. I have to add all in set analysis. Please adivice.

Re: P() set analysis for the discussed example

niharika1234 — Thu, 08 Nov 2018 21:02:02 GMT

Hello,

This is my final Set Analysis:

COUNT({<PRIORITY ={'Tier I'},Year = p(Year), Month = p(MonthName),Quarter = p(Quarter)>} DISTINCT TASK_ID)

but it is not working. I presume there is some error in my set analysis. Please suggest.

Niha

Re: P() set analysis for the discussed example

swuehl — Thu, 08 Nov 2018 23:45:10 GMT

Year, Month and Quarter are part of your calendar table, not connected to TASK_ID, right?

So assigning values to these fields will not filter on TASK_ID values.

You seem to have multiple date fields in your island table. Which one do you want to use for your filter, how does the field values look like and which field could be a corresponding field in your calendar table with the same format?

Besides this, I would recommend linking your new table with the others. If you encounter something like 'it hangs when I try to JOIN', your join may miss common field names and create a cartesian join. I would try to fix this instead of using a data island. But that's my 0.02 €.

Re: P() set analysis for the discussed example

niharika1234 — Fri, 09 Nov 2018 15:55:04 GMT

Hello Stefan,

Thanks. This was Developed by someone else and I am adding New tab of task. when I am adding the % Key only and % DayID it works but when I add %Facilities or the whole Table it stops as it is cartesian Join which is already created .

So My next idea is to make data island of task and all below field of Task uses DUE_DATE as Reporting Month.

So I am thinking lets say Scheduled Task-I expression to be

COUNT({<PRIORITY ={'Tier I'},Year = p(Year), Month = p(MonthName),Quarter = p(Quarter)>} DISTINCT TASK_ID)

But not working.

Do you think I will first Join :

Load

Task_DayID as %DayId

Resident TASK;

Please suggest.

Niha

Re: P() set analysis for the discussed example

swuehl — Fri, 09 Nov 2018 16:09:25 GMT

If you want to link multiple tables by multiple key fields, this will create a synthetic key table. If the number of linking key fields differ from table to table, then this will create nested synthetic keys.

Synthetic keys, especially the nested ones, are hard to validate, it's recommended to create a explicite combined key / link table instead.

Or consider concatenating your tables.

There are discussions in the forum that discuss these topics, search for Synthetic Keys. Like

Synthetic Keys

Coming back to your set analysis: as I tried to explain, if the calendar fields left of the equal signs are not part of your data island table, then these won't filter your TASK_ID.

You would need to decide which of your date fields need to be filtered, and then search for a compatible field in your calendar table.

Assuming formats are matching and fields do exist, something like

Count( {<PRIORITY = {'Tier I'}. DUE_DATE = p(CalendarTable.Date)>} TASK_ID)

Re: P() set analysis for the discussed example

niharika1234 — Fri, 09 Nov 2018 22:07:20 GMT

Amazing!

Count( {<PRIORITY = {'Tier I'}, DUE_DATE = p(%DayId)>} TASK_ID)

It is working!!!