topic IntervalMatch complicated case; 2 common fields in App Development

IntervalMatch complicated case; 2 common fields

Anonymous — Wed, 31 Aug 2016 14:32:51 GMT

Hi.

After days of learning and trying I give up.

Case is like this: I have (actually two big fact tables)

Fact table

Statistics table

Map table for location names

Problem is: How to match statistical data to fact table by date and location although statistical table has more location than fact table. And connect it all to location map to analyze facts by time and location and statisitcs by time and location.

I attach xls and qvf files.

sum of Stat_value should be same for each month obiously and has value ot only for months and location existing in fact table but for all months and locations existing in stat table.

Hope I explained it enough to solve it for some pro.

Hope some pro will help me.

Re: IntervalMatch complicated case; 2 common fields

swuehl — Wed, 31 Aug 2016 20:05:51 GMT

I am not exactely sure what you are looking for, maybe one of the attached QVF gives you some hint how you can achieve what you want.

Re: IntervalMatch complicated case; 2 common fields

Anonymous — Thu, 01 Sep 2016 07:40:04 GMT

Stefan, thanks. It is much closer than I have ever been. But As you can see statistic value in table are multiplied. Why? How to fix it?

Re: IntervalMatch complicated case; 2 common fields

swuehl — Thu, 01 Sep 2016 19:00:05 GMT

I assume you are talking about the third attached QVF.

I've created a stat value for each month in the given period, as you can see in the bar chart when selecting a location.

If you aggregate over all year month values (like in the table when not using the year month dimension), you will get the sum of the stat values.

It seems this is not what you want, but I don't know what you expect to see instead, so maybe elaborate on your requirements, please.

Re: IntervalMatch complicated case; 2 common fields

Anonymous — Fri, 02 Sep 2016 10:00:13 GMT

Actually as resault second and third one qvf are pretty much same.

Look at this screen:

For Selection: STATE1: State1_C

Sum for stat value for every month is ok (1812). Value at top for company is also ok.

But take a look at table on the right for stat value: sum for all locations connected to Location State1_C

(Location7, Location8, Location9) is not truth. According to excel file it should be also 1812

(Location7 : 511 , Location8: 712, Location9: 589).

Why is it wrong? I don't get it.

DATE FROM	DATE TO	STAT_VALUE	LOCATION
01.01.2010	01.06.2011	161	LOCATION1
01.01.2010	01.06.2011	606	LOCATION2
01.01.2010	01.06.2011	569	LOCATION3
01.01.2010	01.06.2011	134	LOCATION4
01.01.2010	01.06.2011	729	LOCATION5
01.01.2010	01.06.2011	746	LOCATION6
01.01.2010	01.06.2011	511	LOCATION7
01.01.2010	01.06.2011	712	LOCATION8
01.01.2010	01.06.2011	589	LOCATION9

Re: IntervalMatch complicated case; 2 common fields

swuehl — Fri, 02 Sep 2016 22:06:31 GMT

As I tried to explained, the values per month are aggregated in the lower right chart.

If you want to see the original base values, you can load the stats table additionally without creating rows per month.

See attached.

Re: IntervalMatch complicated case; 2 common fields

Anonymous — Wed, 09 Nov 2016 09:19:27 GMT

Ok, I get it now. Thank you for your help