Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

klewandowski
New Contributor III

IntervalMatch complicated case; 2 common fields

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: IntervalMatch complicated case; 2 common fields

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.

6 Replies
MVP
MVP

Re: IntervalMatch complicated case; 2 common fields

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.

klewandowski
New Contributor III

Re: IntervalMatch complicated case; 2 common fields

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?

MVP
MVP

Re: IntervalMatch complicated case; 2 common fields

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.

klewandowski
New Contributor III

Re: IntervalMatch complicated case; 2 common fields

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 FROMDATE TOSTAT_VALUELOCATION
01.01.201001.06.2011161LOCATION1
01.01.201001.06.2011606LOCATION2
01.01.201001.06.2011569LOCATION3
01.01.201001.06.2011134LOCATION4
01.01.201001.06.2011729LOCATION5
01.01.201001.06.2011746LOCATION6
01.01.201001.06.2011511LOCATION7
01.01.201001.06.2011712LOCATION8
01.01.201001.06.2011589LOCATION9
MVP
MVP

Re: IntervalMatch complicated case; 2 common fields

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.

klewandowski
New Contributor III

Re: IntervalMatch complicated case; 2 common fields

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

Community Browser