topic Re: Set Analysis based on another date in another table in App Development

Set Analysis based on another date in another table

RsQK — Fri, 24 Jan 2020 08:23:04 GMT

Hi, I've come across a new one for me. In a simplified scenario I have three tables:

Table1: LOAD *, NUM(MONTHSTART(Date)) AS Period.#key, 1 AS Amount; LOAD * INLINE [ ID.#key,Date,User 1,01.01.2020,John 2,05.01.2020,Kenny 3,17.01.2020,Yuri 4,02.02.2020,Max 5,03.02.2020,John 6,04.02.2020,Kenny 7,06.02.2020,Michael 8,09.02.2020,Sandy 9,10.02.2020,Mike 10,17.02.2020,Kate 11,01.03.2020,Hope 12,05.03.2020,Chris 13,13.03.2020,Laura 14,15.03.2020,Lewis 15,19.03.2020,Otto ];

Table2: LOAD *, DATE(MONTHSTART(Date),'MM-YYYY') AS Period, 1 as Amount; LOAD * INLINE [ ID.#key,Date,Type 1,04.01.2020,T 2,01.02.2020,R 3,29.01.2020,T 4,01.03.2020,R 5,07.02.2020,Z 6,05.02.2020,R 7,10.02.2020,T 8,14.02.2020,Z 9,01.03.2020,T 10,01.03.2020,Z 11,04.03.2020,R 12,15.03.2020,R 13,17.03.2020,T 14,24.03.2020,Z 15,30.03.2020,Z ];

Calendar: LOAD DISTINCT [Period.#key], YEAR([Period.#key]) AS Year, MONTH([Period.#key]) AS Month, DATE([Period.#key],'MM-YYYY') AS Period RESIDENT Table1;

And this is where I got stuck. I need to create a table that contains the period from calendar and two measures. One of them is Sum of Amount from Table 1, which isn't a problem at all. But I also need the Sum of Amount from Table 2, where period from Table2 matches the period of calendar (for example, in January of 2020, I'd expect to get 3 as the first measure and 2 as the second measure). I've tackled with P(), done all sorts of things, but just can't get it to work.

Thanks in advance.

Re: Set Analysis based on another date in another table

TimvB — Fri, 24 Jan 2020 08:55:03 GMT

Table1 and Table2 are Fact tables (that include measure data), so you should concatenate the two tables and give the Period field the same field name as in the MasterCalendar table. This way, all fact data is linked to the MasterCalendar data.

Hope it helps!

Re: Set Analysis based on another date in another table

RsQK — Fri, 24 Jan 2020 08:57:18 GMT

Thanks for the response, but concatenation isn't an option in this case.

Re: Set Analysis based on another date in another table

TimvB — Fri, 24 Jan 2020 09:00:44 GMT

Maybe linking the fact tables as described here: http://www.learnallbi.com/link-table-in-qlikview/

Re: Set Analysis based on another date in another table

petter — Fri, 24 Jan 2020 09:07:21 GMT

You could simply use this approach:

Table1:
LOAD *,
NUM(MonthStart(Date1)) AS Period.#key,
1 AS Amount1;

LOAD * INLINE [
ID.#key1,Date1,User
1,01.01.2020,John
2,05.01.2020,Kenny
3,17.01.2020,Yuri
4,02.02.2020,Max
5,03.02.2020,John
6,04.02.2020,Kenny
7,06.02.2020,Michael
8,09.02.2020,Sandy
9,10.02.2020,Mike
10,17.02.2020,Kate
11,01.03.2020,Hope
12,05.03.2020,Chris
13,13.03.2020,Laura
14,15.03.2020,Lewis
15,19.03.2020,Otto
];

Table2:
LOAD *,
NUM(MONTHSTART(Date2)) AS Period.#key,
1 as Amount2;

LOAD * INLINE [
ID.#key2,Date2,Type
1,04.01.2020,T
2,01.02.2020,R
3,29.01.2020,T
4,01.03.2020,R
5,07.02.2020,Z
6,05.02.2020,R
7,10.02.2020,T
8,14.02.2020,Z
9,01.03.2020,T
10,01.03.2020,Z
11,04.03.2020,R
12,15.03.2020,R
13,17.03.2020,T
14,24.03.2020,Z
15,30.03.2020,Z
];

Calendar:
LOAD
[Period.#key],
YEAR([Period.#key]) AS Year,
MONTH([Period.#key]) AS Month,
DATE([Period.#key],'MM-YYYY') AS Period
RESIDENT Table1;

Then you wouldn't need to use Set Analysis at all - you just take advantage of the association between the three tables by period:

Re: Set Analysis based on another date in another table

petter — Fri, 24 Jan 2020 09:18:34 GMT

Linking the three tables on Period.#key will do the trick I believe (according to your stated requirements...), and you wouldn't need to use a Set Expression (aka. Set Analysis):

Table1:
LOAD *,
NUM(MonthStart(Date1)) AS Period.#key,
1 AS Amount1;

LOAD * INLINE [
ID.#key1,Date1,User
1,01.01.2020,John
2,05.01.2020,Kenny
3,17.01.2020,Yuri
4,02.02.2020,Max
5,03.02.2020,John
6,04.02.2020,Kenny
7,06.02.2020,Michael
8,09.02.2020,Sandy
9,10.02.2020,Mike
10,17.02.2020,Kate
11,01.03.2020,Hope
12,05.03.2020,Chris
13,13.03.2020,Laura
14,15.03.2020,Lewis
15,19.03.2020,Otto
];

Table2:
LOAD *,
NUM(MONTHSTART(Date2)) AS Period.#key,
1 as Amount2;

LOAD * INLINE [
ID.#key2,Date2,Type
1,04.01.2020,T
2,01.02.2020,R
3,29.01.2020,T
4,01.03.2020,R
5,07.02.2020,Z
6,05.02.2020,R
7,10.02.2020,T
8,14.02.2020,Z
9,01.03.2020,T
10,01.03.2020,Z
11,04.03.2020,R
12,15.03.2020,R
13,17.03.2020,T
14,24.03.2020,Z
15,30.03.2020,Z
];

Calendar:
LOAD
[Period.#key],
YEAR([Period.#key]) AS Year,
MONTH([Period.#key]) AS Month,
DATE([Period.#key],'MM-YYYY') AS Period
RESIDENT Table1;

Re: Set Analysis based on another date in another table

RsQK — Fri, 24 Jan 2020 09:24:37 GMT

Thanks for the response!

Actually T1 and T2 are linked through the ID.#key, so linking them just by the dates wouldn't suffice.

Re: Set Analysis based on another date in another table

petter — Fri, 24 Jan 2020 10:09:41 GMT

Then you could simply keep the ID.#key common between Table1 and Table2. And you make the Period common between Table1 and Calendar.

Whether you create a data model that connects the tables or do set expressions on-the-fly to connect the right values in various fields and tables or a combination and balance between the two depends on you total application and analysis need.

Re: Set Analysis based on another date in another table

petter — Wed, 05 Feb 2020 14:34:30 GMT

Please click the like button or heck if the response was correct you could even mark it as a correct solution.
Believe me - it will be greatly appriciated by the contributors ...
We love likes as much as anyone posting anything on social media