topic Re: Syntax Help in QlikView

Syntax Help

cbaqir — Wed, 08 Aug 2018 17:01:07 GMT

I have a requirement to show avg number of days from when a generic build resource is populated to when a named resource is populated.

Generic resources = RESOURCE with a "_" in the name like charges_builder.

Requirement: IGNORE generic name of ccl_consult

Requirement: if no "_" (generic) resource, use start date of COMMITTEE_CAT = "ERC - Assigned" to first date name is populated

One DEFECT_ID could have multiple generics or multiple names for each RESOURCE_TYPE

For example, DEFECT_ID 10811 should show the avg days from ClinDoc Resource and Primary Assignee (even though both happen to be the same name here). I would like to see two values since there are two RESOURCE_TYPES and is should count from the 7/31 date NOT 7/19.

How do I write the expression to give me the time that a specific RESOURCE_TYPE went from generic to named?

Thanks!

Re: Syntax Help

sunny_talwar — Wed, 08 Aug 2018 17:06:32 GMT

Not sure, but may be this

SUM(

Aggr(

Max(FACT_ALM_DEFECT_RESOURCE.START_DATE)- Min({<FACT_ALM_DEFECT_RESOURCE.RESOURCE = {'*_*'}>}FACT_ALM_DEFECT_RESOURCE.START_DATE)

,DEFECT_ID)

)

Re: Syntax Help

cbaqir — Wed, 08 Aug 2018 20:06:41 GMT

!Thanks Sunny... but something looks off when I pull that in. Here's another version using a Cyclic Group (cResourceTypeResource).

If you select CCL Resource and then a person, the result confuses me.

Re: Syntax Help

sunny_talwar — Thu, 09 Aug 2018 10:25:33 GMT

I guess I am not sure I understand what you need. Would you be able to provide the expected output based on few different selections?

Re: Syntax Help

cbaqir — Tue, 14 Aug 2018 19:20:41 GMT

6602 should show less than a day but I see 27.03 days?

Re: Syntax Help

sunny_talwar — Tue, 14 Aug 2018 19:28:44 GMT

But this is showing that the max() is 4/2/2018... do you know why? I am not sure I understand your data well enough to answer this... but may be you know why

Re: Syntax Help

cbaqir — Tue, 14 Aug 2018 21:15:35 GMT

I think your screen shot was meant for someone else.

Perhaps I was using the wrong fields.

I have

DM_STAT_ALM_DEFECT_BUILD_RESOURCE.RESOURCE_TYPE

DM_STAT_ALM_DEFECT_BUILD_RESOURCE.RESOURCE

FACT_ALM_DEFECT_RESOURCE.START_DATE

FACT_ALM_DEFECT_RESOURCE.RESOURCE

FACT_ALM_DEFECT_RESOURCE.RESOURCE_TYPE

Re: Syntax Help

cbaqir — Tue, 14 Aug 2018 21:19:10 GMT

What I need is the avg days for EACH Generic *-* resource until a name was populated other than *-*.

For 6602, I would need one value for CCL Resource, ClinDoc Resource, Enterprise Clinical Informaticist, MPage Resource, Primary Assignee and Rules Resource.

Right now, I only see one calculation for the entire ticket.

Re: Syntax Help

sunny_talwar — Wed, 15 Aug 2018 11:44:53 GMT

This is the screenshot I wanted to share

Re: Syntax Help

sunny_talwar — Wed, 15 Aug 2018 11:45:37 GMT

Since you have two resource field... which one you are planning on using?

Re: Syntax Help

cbaqir — Wed, 15 Aug 2018 19:57:57 GMT

Not sure I understand the question. There are several resource type fields.

Re: Syntax Help

cbaqir — Mon, 27 Aug 2018 15:44:24 GMT

Ah - I want to use the right one. I thought it was Fact but maybe it's DM.

Re: Syntax Help

cbaqir — Mon, 27 Aug 2018 18:53:10 GMT

Please see updated requirement for clarity:

Requirement: IGNORE generic name of ccl_consult

Requirement: if no "_" (generic) resource, use start date of COMMITTEE_CAT = "ERC - Assigned" to first date name is populated

Re: Syntax Help

cbaqir — Tue, 28 Aug 2018 14:38:33 GMT

stalwar1‌

Re: Syntax Help

cbaqir — Tue, 25 Sep 2018 18:42:02 GMT

bump