Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peschu123
Partner - Creator III
Partner - Creator III

Concatenation(?) of 2 fact tables with mixed granularity

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 DateEnd Date
1000101.01.201001.10.2010

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

Order noSub-Order no.Start DateEnd Date
1000110001.01.201002.04.2010
1000135010.04.201006.06.2010

and so on like this.

Finally it should look like this:

Order noSub-Order no.Start DateEnd Date
10001001.01.201001.10.2010
1000110001.01.201002.04.2010
1000135010.04.201006.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.

1 Solution

Accepted Solutions
ariel_klien
Specialist
Specialist

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

View solution in original post

7 Replies
ariel_klien
Specialist
Specialist

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
Partner - Creator III
Partner - Creator III
Author

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

peschu123
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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