Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

Data Model - 3 Main Tables

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

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

9 Replies
sunny_talwar

There is two ways to handle data like this (three fact tables).

1) Concatenate the three tables into one table

2) LinkTable to create a implicit join between the three tables

Read about both these options here:

Concatenate vs Link Table

microwin88x
Creator III
Creator III
Author

Hello Sunny. How could you get to know which WORKS/INCIDENTS have TASKS if I try one of those options? Thanks.

sunny_talwar

I will have to go through your post more thoroughly to provide a better answer. I wanted to give a general direction for now. If no body gets back to you, I will try to respond back at a later point.

Best,

Sunny

oscar_ortiz
Partner
Partner

What is the relationship between the Works and Incidents table?

If there is no connection, you'll probably have to follow the Link Table process.

Using your ID_PARENT field to connect to both your ID_INCIDENT and ID_WORK fields.

muthukumar77
Partner
Partner

Hi,

Try this,

WORK_INCIDENT:
LOAD
ID_WORK as ID,
TICKET_TYPE,
DETAIL,
SERVICE,
SUPPORT,
GROUP,
ASSIGNEE,
REQUEST
from WORK;

CONCATENATE(WORK_INCIDENT)

LOAD
ID_INCIDENT as ID,
TICKET_TYPE,
DETAIL,
SERVICE,
SUPPORT,
GROUP,
ASSIGNEE,
REQUEST
from INCIDENT;


RIGHT JOIN(WORK_INCIDENT)


LOAD
ID_TASK,
TICKET_TYPE,
ID_PARENT as ID,
DETAIL,
SUPPORT,
GROUP,
REQUEST_USER,
ZONE
from
TASK;

Muthukumar Pandiyan
avinashelite

Since the task table has both Incidents and the Work (refer ID_Parent column), you can directly join this tables my renaming the ID_PARENT, ID_WORK, ID_INCIDENT as KEY

manucamon
Specialist III
Specialist III

Hi microwin88x,

I ever use autoconcatenate to work with this, because I just can control all Null fields.

MainData:

LOAD

    ID_WORK                    as ID_MAIN,

    TICKET_TYPE,

    DETAIL,

    SERVICE,

    SUPPORT,

    GROUP,

    ASSIGNEE,

    REQUEST_USER,

    ZONE,

    BUILDING,

    VIP_MEMBER,

    ENVIRONMENT,

    CATEGORY1,

    CATEGORY2,

    CATEGORY3,

    CREATE_DATE,

    CREATE_TIME,

    REAL_START_DATE,

    REAL_START_TIME,

    REAL_END_DATE,

    REAL_END_TIME,

    LAST_MODIF_DATE,

    LAST_MODIF_TIME,

    SUBMITTER,

    ADMIN,

    PRIORITY,

    Null()                    as STATUS,

    Null()                    as RESOLUTION,

    Null()                    as CLOSE_DATE,

    Null()                    as CLOSE_TIME,

    Null()                    as IMPACT,

    Null()                    as URGENCY,

    Null()                    as ID_PARENT

RESIDENT Tab1;

MainData:

LOAD

    ID_INCIDENT                    as ID_MAIN,

    TICKET_TYPE,

    DETAIL,

    SERVICE,

    SUPPORT,

    GROUP,

    ASSIGNEE,

    REQUEST_USER,

    ZONE,

    BUILDING,

    VIP_MEMBER,

    ENVIRONMENT,

    CATEGORY1,

    CATEGORY2,

    CATEGORY3,

    CREATE_DATE,

    CREATE_TIME,

    LAST_MODIF_DATE,

    LAST_MODIF_TIME,

    STATUS,

    PRIORITY,

    RESOLUTION,

    CLOSE_DATE,

    CLOSE_TIME,

    IMPACT,

    URGENCY,

    Null()                    as REAL_START_DATE,

    Null()                    as REAL_START_TIME,

    Null()                    as REAL_END_DATE,

    Null()                    as REAL_END_TIME,

    Null()                    as SUBMITTER,

    Null()                    as ADMIN,

    Null()                    as ID_TASK,

    Null()                    as ID_PARENT   

RESIDENT Tab2;

MainData:

LOAD

    ID_TASK                    as ID_MAIN,

    TICKET_TYPE,

    ID_PARENT,

    DETAIL,

    SUPPORT,

    GROUP,

    REQUEST_USER,

    ZONE,

    BUILDING,

    VIP_MEMBER,

    ENVIRONMENT,

    CATEGORY1,

    CATEGORY2,

    CATEGORY3,

    CREATE_DATE,

    CREATE_TIME,

    REAL_START_DATE,

    REAL_START_TIME,

    REAL_END_DATE,

    REAL_END_TIME,

    PRIORITY,

    ENVIRONMENT,

    SUBMITTER,

    LAST_MODIF_DATE,

    LAST_MODIF_TIME,

    Null()                    as SERVICE,

    Null()                    as ASSIGNEE,

    Null()                    as ADMIN,

    Null()                    as STATUS,

    Null()                    as RESOLUTION,

    Null()                    as CLOSE_DATE,

    Null()                    as CLOSE_TIME,

    Null()                    as IMPACT,

    Null()                    as URGENCY   

RESIDENT Tab3;

LEFT JOIN (MainData)

LOAD

    ID_MAIN                    as ID_PARENT,

    TICKET_TYPE                as PARENT_ID_TYPE

RESIDENT MainData;

Regards!

kaveri_bhar123
Contributor II
Contributor II

Hi ,

Since the tables WORKS and INCIDENTS have similar symmetry (have similar columns), so we can concatenate them and join with TASKS table.

Best regards,

Kaveri

Not applicable

Hi,

The best approach is to CONCATENATE the 3 tables :

after changing the column name

ID_work as ID

ID_incident as ID

ID_task as ID

Now your first requirement is satisfied.

Suppose you have Quantity of tickets from incident table  ......you can use:  count({<Ticket type ='Incident '>}ID)

2nd requirement:

How many task belong to Incident.......

if Match(ID,IDparent),count ({<Ticket type ='Incident '>}ID)

if Match(ID,IDparent),count ({<Ticket type ='Work '>}ID)


Thanks

Khushboo