Datamodel Help!!!

[amit_saini]

Hi Folks ,

How to get rid of below $Syn key:

I can create Date &'-'& Facility_Id as %Key , but this might affect data.

Thanks,
AS

[marcus_sommer]

Are you sure that the composed key didn't work properly?

Possible approaches might be to rename one field and to assign both tables with a single key-field or to merge both tables per join/mapping/concatenate into a single table or to create a link-table from both tables. With the given informations isn't it possible to judge which methods is the most practically one.

- Marcus

[amit_saini]

Hi Marcus,

I have tried inner , left join , Join etc but data looks not ok.

Attached is the sample data, could u please help in connecting these 2 tables in right way.

Thanks,
AS

[marcus_sommer]

I'm not sure about your data but I think you are missing a real key-field in both tables. Of course Date and Facility_Id are common and could be used for linking the data but I doubt that this made much sense.

- Marcus

[amit_saini]

Hi Marcus,

I'm not sure what's wrong , looks to me it's an datamodel issue , I tried from all angles , data is 100% correct but looks like still something is not working correct.

Let me explain with below example:

- When I'm using Dimension= Date

                        Expression= Count(Distinct Serial) -----Parts Produced

I'm having below chart and all the numbers are 100% matching with Database.

Result from DB:

Now I want to include on more Dimension -"Shift" to see the Parts produced shift specific and then I found we are having some issue. See the round ones for 4th and 5Th Feb , looks like there is no shift assign to these dates

But if you see in general 4th and 5th Feb is for sure should be part of either of Früh,Nacht or Spät shift.

Not sure what's the issue , attached is sample qvw.  and below is table structure

Please suggest what's the issue.

Script:

LOAD Facility_Id,

    Date(Floor(Date),'DD/MM/YYYY') as Date,

    Month(Date) as Month,

    Year(Date) as Year,

    Week(Date) as Week,

    Day(Date) as Day,

    Product,

    Variant,

    Station,

    Serial,

    Characteristic,

    Values,

    Status,

    DTTSERF

FROM

(qvd);

Interruption:

LOAD Auto,

    Date(Floor(Date),'DD/MM/YYYY') as Date,

    Month(Date) as Month,

    Year(Date) as Year,

    Week(Date) as Week,

    Day(Date) as Day,

    Facility_Id,

    Shift,

    Line_Name,

    Product_Name,

    Variant_Name,

    Station_Name,

    Station_Label,

    Type_Name,

    Area_Name,

    Reason_Name,

    Standard_Name,

    Why_Name,

    Quantity,

    Comments,

    Production

FROM

(qvd);

Thanks,

AS

[marcus_sommer]

Like above mentioned you need a real key to associate both tables - Date and Facility_Id are common fields but no valid key. Therefore I suggest you tried to extend the data-sources to it.

- Marcus