topic Re: One to Many Issue in QlikView

One to Many Issue

Fri, 29 Apr 2011 17:46:11 GMT

I have sales "detail" tables that provides information down to the stock code level by day, but I have a budget file that is only at the Product Class level for each month. Each stock code has a Product Class. I need to see detail information and also summary information compared to budget. My linking current creates a one to many problem. How do I do this?

Thanks,

Stephen

One to Many Issue

erichshiino — Sat, 30 Apr 2011 15:49:44 GMT

Hi, Stephen

I would concatenate everything on a single Fact Table. I woudl associate the budget with the first date of the month (could be anydate, actually).

The budget will have null values for the stock code dimension.

I would solve the rest on the interface.

I only allow the user to compare sales and budget at the Product Class and Month level.

If the user selects a stock code, the budget will be null, which is ok because there is no budget at this level.

If the user selects a day, he will not get a budget either, because there is no daily budget.

I included an application to show this in action.

I used different fields to include the sales result (VALUE) and BUDGET. You could use a single field to put the number and an additional field with a flag.

Hope it helps,

One to Many Issue

Sat, 07 May 2011 15:17:52 GMT

Hi Erich,

In your attached example, what if a user selected SKU 1 and wants to return the following in Table 3?

a) Value = 50

b) Budget = 100, essentially the Budget for Product Class A in Jan

Re: One to Many Issue

erichshiino — Sat, 07 May 2011 20:10:58 GMT

I see three options here.

I describe them, but also included on the example file:

Budget1:

sum( {<SKU= >}BUDGET) - ignores the selecion on SKU. This may generate results for others lines

Budget2:

= IF( SUM(VALUE)>0, sum( {<SKU= >}BUDGET)) - this will show the buget of the category for the product associated with the SKU (check an error at the total line)

Budget3:

SUM( AGGR( IF( SUM(VALUE)>0, sum( {<SKU= >}BUDGET)), [PRODUCT CLASS],PERIOD))

The third option (recommended) will do basically the same thing that the second, but will reproduce the results on the total line.

Hope it helps,

Erich

One to Many Issue

Sat, 07 May 2011 23:12:28 GMT

Thanks for the quick response Erich. It did help.

As an extension to the answer, if SKU 1 is selected again, Budget3 returns 100 for the January Period.

Would it be possible to amend Budget 3 such that it returned the value for Feb as well? basically returning the Budget value for Product Class A for all months based on the SKU selection?

Prod Class Month Value Budget

A Jan 50 100

A Feb 0 110

Hope you're able to help on this one.

Re: One to Many Issue

erichshiino — Sun, 08 May 2011 16:05:16 GMT

Hi,

I created another function:

In this case, I use the product class which has some value at any point in time. (Indirect Set analysis.

sum( {<SKU= , [PRODUCT CLASS]={'=SUM(VALUE)>0'}>}BUDGET)

Please, check attachment too.

Best Regards,

Erich Shiino

Re: One to Many Issue

Tue, 10 May 2011 00:44:50 GMT

I have checked your most recent suggestion and the code works.

I have one further complication, there's an additional field attached to the Budget table called "Country". Using SKU 1 as the filter, your formula returns the correct result as follows:

Period Value Budget

Jan 50 100

Feb - 110

I am trying to get the following result, without much success with my limited knowledge of set analysis, and have tried to modify your suggestion but afraid I am probably doing more harm than good.

Where a user makes a selection on Country, I need the Value to stay static at 50, but the Budget to reflect the selection on Country, as follows:

Period Value Budget Country

Jan 50 100 =X

Feb - 110

Period Value Budget Country

Feb 50 110 =Y

Ultimately, the Value will be divided by the Budget to get a ratio in a graph format.

Regards

Re: One to Many Issue

erichshiino — Tue, 10 May 2011 01:03:11 GMT

I think the actuals should have the same dimension. Then it would make more sense to compare it with the budget.

If you need to keep it like this, I'm afraid my solution will not be so general as the others.

I created a second country field (refCountry) which is a data island (disconnected from the model). This way you can make selections in sku and still have something to select on country (or refCountry).

I reconnect this field only on the expression level using p().

I say that for the budget, I must not consider sku (because this level of detail does not exist) but I say the country must obey the selection in refCountry.

Maybe it's better to understand it at the application.

Hope it helps,

Erich

Re: One to Many Issue

Tue, 10 May 2011 01:41:41 GMT

Thanks for the quick response Erich. Your solution definitely works based on the sample qvw.

My application is more complicated that the example with many more "Country" type fields, therefore may not be suitable.

I may have to retest the calendar island solution with the two fact tables joined by the ProdClass using SetAnalysis, which I hope would perform better than the IF statement.

Let you know how I go. Thanks for all your generous help.