topic Re: Facts Concatenation in QlikView

Facts Concatenation

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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..

Re: Facts Concatenation

Anonymous — Tue, 21 Nov 2017 08:56:07 GMT

Hi Naresh,

As a first thing try to add some identifier (flag) in bot the tables. And pull the required fields and check the data by selecting flag fields.

some thing like below

Fact1:

Supplier_ID,Plant_ID,Calendar_Date,Material_ID,Sales,1 as Flag

From XXX;

Cancatenate(Fact1)

Fact2:

Plant_ID,Material_ID,Calendar_Date,Count_of_Parts_Consumed,2 as Flag;

From XXX;

Also convert the Date in to Month level if you need

-Jai

Re: Facts Concatenation

madhumitha — Tue, 21 Nov 2017 09:00:16 GMT

Hi,

In the above example, you have only Plant_id as common field.

if you pull Plant_ID and count(Count_of_Parts_Consumed), you will see values.

However, all the values of Sullpier_ID will be null for Count_of_Parts_Consumed.

Since there is no evaluated expression the straight table wont show any values.

Hope this helps.

Thanks!

Re: Facts Concatenation

Anil_Babu_Samineni — Tue, 21 Nov 2017 09:06:35 GMT

Can you share full version of expression you used?

Re: Facts Concatenation

nareshthavidishetty — Tue, 21 Nov 2017 09:10:09 GMT

DIm : Supplier_ID

Exp: =Sum(Sales) / count(Count_of_Parts_Consumed)*1000000

Re: Facts Concatenation

devarasu07 — Tue, 21 Nov 2017 09:11:33 GMT

try to create composite key like below way and it should work.

Fact1:

Supplier_ID,

//Plant_ID,

//Calendar_Date,

//Material_ID,

Sales,

Plant_ID &'|'& Date(Calendar_Date) &'|'& Material_ID as %Key

From XXX;

Cancatenate(Fact1)

Fact2:

//Plant_ID,

//Material_ID,

//Calendar_Date,

Count_of_Parts_Consumed,

Plant_ID &'|'& Date(Calendar_Date) &'|'& Material_ID as %Key

From XXX;

in addition also check this article,

Fact Table with Mixed Granularity

Thanks,

Deva

Re: Facts Concatenation

Anil_Babu_Samineni — Tue, 21 Nov 2017 09:14:08 GMT

And your data model ??

Re: Facts Concatenation

PrashantSangle — Tue, 21 Nov 2017 09:18:58 GMT

Hi,

as suggested by Madhumita both field i.e. supplier id and Count_of_Parts_Consumed are coming from 2 different fact table.

So they are not interrelated in your data model, hence you are getting 0.

Regards,

Re: Facts Concatenation

jonathandienst — Tue, 21 Nov 2017 09:24:59 GMT

>> DIm : Supplier_ID

>> Exp: =Sum(Sales) / count(Count_of_Parts_Consumed)*1000000

The Supplier_ID dimension will only associate with the records loaded as Fact1. The records containing Count_of_Parts_Consumed are excluded. Perhaps partial totalling would work:

Sum(Sales) / count(TOTAL <Plant_ID, Calendar_Date, Material_ID> Count_of_Parts_Consumed)*1000000

If that does not work, you may have to relook your datamodel

Re: Facts Concatenation

nareshthavidishetty — Tue, 21 Nov 2017 09:43:22 GMT

Hi,

This is how the fact tables got concatenated.

Fact1 is to get get the details of parts which are not good.

Fact2 is for how many parts consumed.

[Fact Quality Parts]:

LOAD text(Notification_Id) as [Notification ID],

text(Disposition_Code) as [Disposition Code],

Conformed_Cause_Code as [Conformed Cause Code],

Conformed_Location_Code as [Conformed Location Code],

text( Plant_Id) as [Plant ID],

Disposition_Code_Group as [Disposition Code Group],

text( Material_Id) as [Material ID],

text(Plant_Material_Id) as [Plant Material ID],

text(QNR_Reporting_Unit_Code) as [QRU Code],

text(Supplier_Id) as [Supplier ID],

Manufacturer_Id as [Manufacturer ID],

date(Calendar_Date,'DD-MMM-YYYY') as [Calendar Date],

month(Calendar_Date) as Month,

Nr_Of_Quality_Parts as [# QNs]

FROM [..\5_QVD\Notification.qvd] (qvd);

Concatenate([Fact Quality Notification])

[Fact Material Parts]:

LOAD Calendar_Month as [Calendar Month],

Calendar_Year&''&Calendar_Month as [Year Month],

Calendar_Year as [Calendar Year],

Date(Floor(MonthEnd(Date#(Calendar_Year & '-' & Calendar_Month, 'yyyy-MM'))), 'DD-MMM-YYYY') as [Calendar Date],

Month_Material_Movement_Count as [Monthly Material Movement Count],

Conformed_Movement_Type as [Conformed Movement Type],

text(Plant_Id) as [Plant ID],

text(Material_Id) as [Material ID],

Plant_Material_Id as [Plant Material ID]

FROM [..\5_QVD\Movement.qvd] (qvd);

Thanks..

Re: Facts Concatenation

nareshthavidishetty — Tue, 21 Nov 2017 09:47:24 GMT

Hi Jonathan,

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

Thanks..

Re: Facts Concatenation

amarnath_krishn — Tue, 21 Nov 2017 10:53:48 GMT

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.

Re: Facts Concatenation

nareshthavidishetty — Wed, 22 Nov 2017 06:29:06 GMT

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..

Re: Facts Concatenation

amarnath_krishn — Wed, 22 Nov 2017 09:23:44 GMT

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.

Re: Facts Concatenation

Anonymous — Wed, 22 Nov 2017 09:46:03 GMT

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

Re: Facts Concatenation

nareshthavidishetty — Fri, 24 Nov 2017 10:59:09 GMT

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..