topic Re: Data model - avoiding loop? in QlikView

Data model - avoiding loop?

twanqlik — Fri, 28 Apr 2017 14:20:23 GMT

I can use some help with my data model

I have 2 dimensions in my data model that are using the same field:

Dim_SourceSystem
Dim_BMC

The field that in both dimensions is used is: Business_Code

Renaming one of the fields is no option because:

- On the front-end the user should be able to filter on a business_code that will filter in both the dimensions.

How my calculation should work:

User selects one or more business codes
Qlikview calculates the count on [Distinct CONSUMER_ID] in the FACT_Consumer_Sell_Out table.

Linking the 2 Business_Code fields results in a loop. I currently have no idea how to fix this. Help

Background:

a) product registrations are linked to a business code

b) the used source code is sometimes also linked to a business code

A consumer can have a product registration, the product registration is linked to a Business_Code

A consumer registration has always a source code, and a Business_Code is sometimes linked to a source code.

The user wants to know how many consumers belong to his Business Code, that are consumers with and without a product registration.

Re: Data model - avoiding loop?

marcus_sommer — Fri, 28 Apr 2017 15:14:39 GMT

I think I would transfer Business_Code into the fact-table probably with one (or two nested) mapping(s) and applymap() and removing them from the dimension-tables.

- Marcus

Re: Data model - avoiding loop?

spividori — Fri, 28 Apr 2017 15:17:47 GMT

Hi.

Could you attach an excel sheet with data to help you ?.

Regards.

Re: Data model - avoiding loop?

twanqlik — Fri, 28 Apr 2017 16:19:35 GMT

Yes of course, please find attached a sample

Re: Data model - avoiding loop?

jcampbell474 — Fri, 28 Apr 2017 18:44:41 GMT

Please reload the attached.

Re: Data model - avoiding loop?

twanqlik — Mon, 01 May 2017 07:38:55 GMT

Thank you Jason, however i don't see any difference in your attachment?

Re: Data model - avoiding loop?

jcampbell474 — Mon, 01 May 2017 11:46:47 GMT

I commented out your script and added Tab 2 in the Script Editor. I can't reload because I don't have the source data.

Re: Data model - avoiding loop?

spividori — Tue, 02 May 2017 10:19:12 GMT

Hi.

See the attachment. Hope this helps.

Regards.

Re: Data model - avoiding loop?

twanqlik — Wed, 10 May 2017 14:58:03 GMT

Thanks Sandro, but it's not completely working.

When I select consumerID 8 none of the business unit codes are selected, but the business unit code '0368' should be selected.

Any ideas on how to improve?