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: 
nareshthavidishetty
Creator III
Creator III

Facts Concatenation

Hi,

We are having two fact tables of huge dataset.

Fact1:

Supplier_ID,Plant_ID,Calendar_Date,Material_ID,Sales

From XXX;

Cancatenate(Fact1)

Fact2:

Plant_ID,Material_ID,Calendar_Date,Count_of_Parts_Consumed;

From XXX;

When i try to Pull Sullpier_ID and count(Count_of_Parts_Consumed) in a straight table I haven't get any detail.

Fact2 Table is aggregated by month in backend and Fact1 is day level.

Our datmodel is strict to use concatenation when we have two fact tables.

Thanks..

15 Replies
nareshthavidishetty
Creator III
Creator III
Author

Hi Jonathan,

After using the approach as said earlier  we are getting the values as below attached.

Thanks..

amarnath_krishn
Contributor III
Contributor III

Count(Parts_consumed) as stated above comes from a different fact unrelated to Supplier ID.

You might have to get the fact2 values aggregated at supplier level in the fact, if you have to represent the values at supplier level.

nareshthavidishetty
Creator III
Creator III
Author

Hi,

Is there any smart way.

Rules to be followed in data model.

Due to more data(Ex: for only 6 months we have 30 million it might be increase more day by day) if we have two facts we need to do concatenation instead of join.

Thanks..

amarnath_krishn
Contributor III
Contributor III

Based on the information shared above, the 2 facts are having just a relationship based on the keys:

[Plant ID], [Material ID], and [Plant Material ID].

However, the second fact is an aggregated one at a bit higher level and for a said month, there could be multiple suppliers involved. So, you cannot use applymap or join to get supplier level parts count. 

So, if you really want the straight table built as stated above  - please get the second fact aggregated at supplier level in the backend for each month and concat it to the first fact.

Anonymous
Not applicable

Hi,

It seems to be two fact tables with different granularity. You can refer below link if it is helpful to you.

Fact Table with Mixed Granularity

Thanks,

Harshit

nareshthavidishetty
Creator III
Creator III
Author

Hi,

All were might be correct.

But for final luck..

Please find the attached Sample data.If any one got answer please provide script.

I don't have access to open QVW's.

Thanks..