topic Re: Data Model - 3 Main Tables in QlikView

Data Model - 3 Main Tables

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

Hello,

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

WORKS:

ID_WORK	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
W001	Work	Password Issue	SRV001	Helpdesk	Helpdesk	USER001	REQUSER015	D	IV	NO	3	APPS	SAP	REQUIREMENT	15/05/2016	11:20:13	18/05/2016	11:21:13	3/6/2016	2:45:32	03/06/2016	2:45:32	USR001	ADM001	Low
W002	Work	Printer Config	SRV002	Helpdesk	Helpdesk	USER002	REQUSER016	D	V	NO	1	APPS	OFFICE	REQUIREMENT	15/05/2016	12:00:10	18/05/2016	12:00:10	3/6/2016	2:45:32	03/06/2016	2:45:32	USR002	ADM003	Low
W003	Work	Requirement	SRV001	Helpdesk	Helpdesk	USER003	REQUSER017	A	I	NO	3	PHONE	ADMIN	ISSUE	15/05/2016	11:57:50	18/05/2016	11:57:50	3/6/2016	2:45:31	03/06/2016	2:45:35	USR003	ADM006	Low
W004	Work	Service Config	SRV003	Technology	IT	USER005	REQUSER015	B	II	NO	0	APPS	SOFTWARE	REQUIREMENT	15/05/2016	10:35:03	18/05/2016	13:05:07	22/05/2016	2:45:01	22/05/2016	2:45:08	USR004	ADM007	Low

INCIDENTS:

ID_INCIDENT	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
I001	Incident	Mail Support	SRV005	Reports	Web 2.0	USER008	REQUSER010	A	I	NO	0	APPS	SOFTWARE	ERROR	18/05/2016	17:52:55	30/05/2016	2:00:12	Closed	High	Start	04/06/2016	2:00:12	2	High
I002	Incident	Software Install	SRV007	Reports	Web 2.0	USER009	REQUSER011	A	I	YES	1	APPS	SOFTWARE	ERROR	18/05/2016	20:15:29	30/05/2016	14:10:42	Closed	Low	Transfer	30/05/2016	14:10:43	4	Low
I003	Incident	Phone Renewal	SRV001	Reports	IT	USER001	REQUSER012	A	II	YES	3	APPS	SOFTWARE	ERROR	19/05/2016	20:58:28	19/05/2016	2:15:45	Closed	Low	Recieved	19/05/2016	2:11:45	3	Low
I004	Incident	Process Error	SRV003	Technology	IT	USER002	REQUSER012	C	III	NO	1	APPS	SOFTWARE	ERROR	20/05/2016	12:40:17	22/05/2016	2:00:03	Closed	Low	Done	22/05/2016	2:00:03	3	Low

TASKS:

ID_TASK	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
T001	Task	I003	Permissions Issue		Edenor 2.0	REQUSER010	A	I	YES	0	APPS	SOFTWARE	ERROR	18/05/2016	17:50:15	31/05/2016	16:55:40			High	0	USR005	31/05/2016	16:55:40
T002	Task	I004	Folder Request	Technology	Operations	REQUSER011	B	II	NO	1	APPS	SOFTWARE	REQUIREMENT	18/05/2016	12:56:59	31/05/2016	14:15:12	18/05/2016	14:15:52	High	1	USR007	18/05/2016	14:15:52
T003	Task	W001	Process Config	Technology	Operations	REQUSER012	B	I	NO	0	APPS	SOFTWARE	REQUIREMENT	18/05/2016	13:05:45	18/05/2016	14:09:43	18/05/2016	14:09:43	Low	0	USR009	18/05/2016	14: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!!!

Re: Data Model - 3 Main Tables

sunny_talwar — Thu, 16 Jun 2016 18:55:58 GMT

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

Re: Data Model - 3 Main Tables

microwin88x — Thu, 16 Jun 2016 19:50:58 GMT

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

Re: Data Model - 3 Main Tables

sunny_talwar — Thu, 16 Jun 2016 19:52:33 GMT

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

Re: Data Model - 3 Main Tables

oscar_ortiz — Thu, 16 Jun 2016 22:05:09 GMT

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.

Re: Data Model - 3 Main Tables

muthukumar77 — Fri, 17 Jun 2016 05:00:30 GMT

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;

Re: Data Model - 3 Main Tables

avinashelite — Fri, 17 Jun 2016 05:14:39 GMT

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

Re: Data Model - 3 Main Tables

Anonymous — Fri, 17 Jun 2016 07:01:29 GMT

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!

Re: Data Model - 3 Main Tables

Anonymous — Fri, 17 Jun 2016 07:40:37 GMT

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

Re: Data Model - 3 Main Tables

Fri, 17 Jun 2016 08:26:02 GMT

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