Concatenation(?) of 2 fact tables with mixed granularity

[peschu123]

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.

[ariel_klien · Accepted solution]

Hi

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

BR

Ariel

[marcus_sommer]

Here your app back.

- Marcus

[peschu123]

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

[peschu123]

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?

[marcus_sommer]

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

[peschu123]

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?

[marcus_sommer]

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