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

Data Model - Multiple Facts

Hello,

I have 3 main tables: Works, Incidents and Tasks.

WORKS:

ID_WORKTICKET_TYPEDETAILSERVICESUPPORTGROUPASSIGNEEREQUEST_USERZONEBUILDINGVIP_MEMBERENVIRONMENTCATEGORY1CATEGORY2CATEGORY3CREATE_DATECREATE_TIMEREAL_START_DATEREAL_START_TIMEREAL_END_DATEREAL_END_TIMELAST_MODIF_DATELAST_MODIF_TIMESUBMITTERADMINPRIORITY
W001WorkPassword IssueSRV001HelpdeskHelpdeskUSER001REQUSER015DIVNO3APPSSAPREQUIREMENT15/05/201611:20:1318/05/201611:21:133/6/20162:45:3203/06/20162:45:32USR001ADM001Low
W002WorkPrinter ConfigSRV002HelpdeskHelpdeskUSER002REQUSER016DVNO1APPSOFFICEREQUIREMENT15/05/201612:00:1018/05/201612:00:103/6/20162:45:3203/06/20162:45:32USR002ADM003Low
W003WorkRequirementSRV001HelpdeskHelpdeskUSER003REQUSER017AINO3PHONEADMINISSUE15/05/201611:57:5018/05/201611:57:503/6/20162:45:3103/06/20162:45:35USR003ADM006Low
W004WorkService ConfigSRV003TechnologyITUSER005REQUSER015BIINO0APPSSOFTWAREREQUIREMENT15/05/201610:35:0318/05/201613:05:0722/05/20162:45:0122/05/20162:45:08USR004ADM007Low

INCIDENTS:

ID_INCIDENTTICKET_TYPEDETAILSERVICESUPPORTGROUPASSIGNEEREQUEST_USERZONEBUILDINGVIP_MEMBERENVIRONMENTCATEGORY1CATEGORY2CATEGORY3CREATE_DATECREATE_TIMELAST_MODIF_DATELAST_MODIF_TIMESTATUSPRIORITYRESOLUTIONCLOSE_DATECLOSE_TIMEIMPACTURGENCY
I001IncidentMail SupportSRV005ReportsWeb 2.0USER008REQUSER010AINO0APPSSOFTWAREERROR18/05/201617:52:5530/05/20162:00:12ClosedHighStart04/06/20162:00:122High
I002IncidentSoftware InstallSRV007ReportsWeb 2.0USER009REQUSER011AIYES1APPSSOFTWAREERROR18/05/201620:15:2930/05/201614:10:42ClosedLowTransfer30/05/201614:10:434Low
I003IncidentPhone RenewalSRV001ReportsITUSER001REQUSER012AIIYES3APPSSOFTWAREERROR19/05/201620:58:2819/05/20162:15:45ClosedLowRecieved19/05/20162:11:453Low
I004IncidentProcess ErrorSRV003TechnologyITUSER002REQUSER012CIIINO1APPSSOFTWAREERROR20/05/201612:40:1722/05/20162:00:03ClosedLowDone22/05/20162:00:033Low

TASKS:

ID_TASKTICKET_TYPEID_PARENTDETAILSUPPORTGROUPREQUEST_USERZONEBUILDINGVIP_MEMBERENVIRONMENTCATEGORY1CATEGORY2CATEGORY3CREATE_DATECREATE_TIMEREAL_START_DATEREAL_START_TIMEREAL_END_DATEREAL_END_TIMEPRIORITYENVIRONMENTSUBMITTERLAST_MODIF_DATELAST_MODIF_TIME
T001TaskI003Permissions IssueEdenor 2.0REQUSER010AIYES0APPSSOFTWAREERROR18/05/201617:50:1531/05/201616:55:40High0USR00531/05/201616:55:40
T002TaskI004Folder RequestTechnologyOperationsREQUSER011BIINO1APPSSOFTWAREREQUIREMENT18/05/201612:56:5931/05/201614:15:1218/05/201614:15:52High1USR00718/05/201614:15:52
T003TaskW001Process ConfigTechnologyOperationsREQUSER012BINO0APPSSOFTWAREREQUIREMENT18/05/201613:05:4518/05/201614:09:4318/05/201614:09:43Low0USR00918/05/201614:09:45

Each table has an ID:

  • For WORKS: W001, W002, W003
  • For INCIDENTS: I001, I002, I003
  • For TASKS: T001, T002, T003

First of all, I need to have a UNIQUE ID to manage all IDs from all 3 tables. For example, having an ID_MAIN where I could have all values from: ID_WORK, ID_INCIDENT and ID_TASK (W00x, I00x and T00x) with frequency = 11 on this case (4+4+3). This would be a common ID with values: W001, W002, W003, W004, I001, I002, I003, I004, T001, T002, T003 so I could see all of them and search them on a same ID.

As you may have noticed, I have many common fields over the 3 tables. And there are also specific fields for every table.

I wanted to know which is the best way to create my data model. My general request is the following so far:

- Quantity of Tickets (Works/Incidents/Tasks) by: CREATE_DATE/GROUP/CATEGORY1/STATUS/PRIORITY

- Another thing to considerate is the ID_PARENT in table TASKS. The thing is that each TASK is assigned to a WORK or INCIDENT. That means that for example if I have in table TASKS ID_MAIN=T001, I could have ID_PARENT=I003, meaning that TASK belongs to an INCIDENT. I say this because I will have to determine which tasks are assigned to a WORK or an INCIDENT. Please, note that the field ID_PARENT is only available in table TASKS.

How would you solve this?

Thank you!

5 Replies
Anonymous
Not applicable

Hi microwin,

You have answered the same on Data Model - 3 Main Tables‌, isn't it?

Regards!

effinty2112
Master
Master

Hi,

     Try something like this:

Data:

LOAD ID_WORK as ID,

     TICKET_TYPE,

     DETAIL,

etc.

From Works Source Table;


Concatenate (Data)

LOAD

  ID_INCIDENT as ID,

     TICKET_TYPE,

     DETAIL,

etc

From Incidents Source Table;

Concatenate (Data)

LOAD ID_TASK,

     TICKET_TYPE,

     ID_PARENT as ID,

     DETAIL,

etc.

From Tasks Source Table;

Now when you select an ID with a task associated with it, such as I003, the data model will also select records of TICKET_TYPE Task that belong to that ID.

TICKET_TYPE ID ID_TASK
IncidentI001
IncidentI002
IncidentI003
IncidentI004
TaskI003T001
TaskI004T002
TaskW001T003
WorkW001
WorkW002
WorkW003
WorkW004

Good luck

Andrew

microwin88x
Creator III
Creator III
Author

Hi Manuel, yes but I was trying to find some other answers for a better or more precise solution to my requirement.

CarlosAMonroy
Creator III
Creator III

Hi,

If you want to handle this in a easily way:

Join the ID_PARENT to WORKS and INCIDENTS table

Create a complex key using the ID_PARENT and the key of each table

Add a new flag field to distinct the source data

Concatenate them

Thanks,

Carlos

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi,

there are two approaches to this:

Link table

Concatenated Fact table.

The approach given above by Andrew is a concatenated fact, which in my experience tends to be the better approach.

Marcus