topic Re: Concatenation(?) of 2 fact tables with mixed granularity in QlikView

Concatenation(?) of 2 fact tables with mixed granularity

peschu123 — Thu, 25 Jul 2013 12:05:42 GMT

I tried to concatenate 2 Fact tables but it seems to be a bit tricky. I've read several documents from Henric(perhaps thats why the title sounds like this) but it drives me crazy and won't work. 😞

I hope that there is somebody out there who can help me with the concatenation. See the attached sample files or my description. I created some simple data, the reality sadly is a "bit" more complex.

Description in short:

Table1 - has orders and dates

Order no	Start Date	End Date
10001	01.01.2010	01.10.2010

Table2 - some kind of sub-orders(dont know how to call it correctly in english)

Order no	Sub-Order no.	Start Date	End Date
10001	100	01.01.2010	02.04.2010
10001	350	10.04.2010	06.06.2010

and so on like this.

Finally it should look like this:

Order no	Sub-Order no.	Start Date	End Date
10001	0	01.01.2010	01.10.2010
10001	100	01.01.2010	02.04.2010
10001	350	10.04.2010	06.06.2010

In my sample file I added some flag files for the final table, to make life easier in charts etc. but the concatenation is my main problem.

I think a key like "Order no¦Sub-Order no." or "10001¦0" would be neccessary too.

I appreciate any help.

Re: Concatenation(?) of 2 fact tables with mixed granularity

ariel_klien — Thu, 25 Jul 2013 12:22:18 GMT

You can do this in the script:

Table:

Load

[Order no],

0 as [Sub-Order no],

[Start Date],

[End Date]

FROM ...Table1.

Concatenate

Load

[Order no],

[Sub-Order no],

[Start Date],

[End Date]

FROM ....Table2

Ariel

Re: Concatenation(?) of 2 fact tables with mixed granularity

marcus_sommer — Thu, 25 Jul 2013 12:26:06 GMT

Here your app back.

- Marcus

Re: Concatenation(?) of 2 fact tables with mixed granularity

peschu123 — Thu, 25 Jul 2013 12:28:05 GMT

Thank you very much ... man I was a bit to complicated in my head

Re: Concatenation(?) of 2 fact tables with mixed granularity

peschu123 — Thu, 25 Jul 2013 13:12:33 GMT

thx / Danke Marcus

I feel a bit stupid right now ^^ 30 degree at the office are not very helpful at these topics.

By the way:

What is the "best" value for field "sub-order" in the first row (actually it is Order 10001 and sub-order 0 ). Because in the first row there is no sub-order in reality.

I think of cases where I want to count the suborders for an order. Or does the value not matter and it's better to use a flag field for that?

Re: Concatenation(?) of 2 fact tables with mixed granularity

marcus_sommer — Thu, 25 Jul 2013 14:21:03 GMT

Is there not a large amount of data (its relative ...) you could use 0 and then count(if(Vorgang >0, Vorgang), otherwise create better a extra flag field with 0 and 1 by sub-orders und then sum(flag).

- Marcus

Re: Concatenation(?) of 2 fact tables with mixed granularity

peschu123 — Thu, 25 Jul 2013 14:48:51 GMT

Hi Marcus,

thank you for your reply.

I would say the amount of data can be called large. There are about 500k+ rows for this table(ok not the same like 50 mio). As you mentioned it is relative.

I assume summing up the flag field is faster? Is there a difference in memory usage if I use null() instead of 0 or 1?

Re: Concatenation(?) of 2 fact tables with mixed granularity

marcus_sommer — Thu, 25 Jul 2013 14:58:55 GMT

sum(flag) should be the fastest possibility. Memory usage could be a little bit higher as without flag-field, because it is extra. Towards 0/1 or null() I assume it is about the same.

- Marcus