topic Re: Datamodel Help!!! in QlikView

Datamodel Help!!!

amit_saini — Wed, 22 Feb 2017 07:32:40 GMT

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

Re: Datamodel Help!!!

marcus_sommer — Wed, 22 Feb 2017 07:56:11 GMT

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

Re: Datamodel Help!!!

amit_saini — Wed, 22 Feb 2017 10:16:58 GMT

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

Re: Datamodel Help!!!

marcus_sommer — Wed, 22 Feb 2017 15:15:34 GMT

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

Re: Datamodel Help!!!

amit_saini — Thu, 02 Mar 2017 05:11:45 GMT

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,

Re: Datamodel Help!!!

marcus_sommer — Thu, 02 Mar 2017 12:48:39 GMT

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