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: 
Anonymous
Not applicable

Multiple Fact Tables Data Modeling

Hi,

I am using QlikView for last one month, it is very good reporting tool. I created one data model and prepared the reports, but I am getting the duplicate data in the reports.

More Info:

I have two tables TRANSACTIONS, TICKETS with below fields. I want to create data model using these two tables. Here master_doc_id as the key between these two tables.

TRANSACTIONS:

    order_no,                // order number

    eu_master_doc_id, // end user id

    res_master_doc_id, // reseller id

    order_date,

    mant_end_date,

    region,

    product

    etc.....

TICKETS:

ticket_id,

master_doc_id // it can be end user id or reseller id or both

ticket_open_date,

ticket_soved_date,

etc..

Important Notes:

1. [TRANSACTIONS] : At order line item level eu_master_doc_id, res_master_doc_id  can have different or same values

2. [TICKETS]: At ticket_id level master_doc_id can have the value which can be belong to end user id or reseller id or both

Please find the data model below for your reference. My problem is while creating the reports I am getting duplicate data.

It would be a great help.

Please let me know if you need more details.

Thank you!

Samanth

Data Model:

Using the above two tables, I have created the below data model.

data_model_image.png

Data Model Script:

TRANS_REF:
LOAD DISTINCT order_no, eu_master_doc_id as master_doc_id, 'enduser' as cust_type Resident TRANSACTIONS ;
OUTER JOIN
LOAD DISTINCT order_no, res_master_doc_id as master_doc_id, 'reseller' as cust_type Resident TRANSACTIONS;
//
EU_MID2OrderDateMap:
Mapping LOAD DISTINCT eu_master_doc_id , order_dt Resident TRANSACTIONS;
RES_MID2OrderDateMap:
Mapping LOAD DISTINCT res_master_doc_id, order_dt Resident TRANSACTIONS;
EU_MID2MaintEndDateMap:
Mapping LOAD DISTINCT eu_master_doc_id, maint_end_dt Resident TRANSACTIONS;
RES_MID2MaintEndDateMap:
Mapping LOAD DISTINCT res_master_doc_id, maint_end_dt Resident TRANSACTIONS;
TKT_MID2CreatedDateMap:
Mapping LOAD DISTINCT zen_master_doc_id, ticket_open_date Resident TICKETS;
TKT_MID2SolvedDateMap:
Mapping LOAD DISTINCT zen_master_doc_id, ticket_solved_date Resident TICKETS;
DateBridge:
    Load DISTINCT eu_master_doc_id as master_doc_id, Applymap('EU_MID2OrderDateMap', eu_master_doc_id,Null()) as EventDate, 'eu_order_date' as EventDateType
    Resident TRANSACTIONS;
    Load DISTINCT res_master_doc_id as master_doc_id, Applymap('RES_MID2OrderDateMap', res_master_doc_id,Null()) as EventDate, 'res_order_date' as EventDateType
    Resident TRANSACTIONS;
    Load DISTINCT eu_master_doc_id as master_doc_id, Applymap('EU_MID2MaintEndDateMap', eu_master_doc_id,Null()) as EventDate, 'eu_maint_date' as EventDateType
    Resident TRANSACTIONS;
    Load DISTINCT res_master_doc_id as master_doc_id, Applymap('RES_MID2MaintEndDateMap', res_master_doc_id,Null()) as EventDate, 'res_maint_date' as EventDateType
    Resident TRANSACTIONS;
    Load DISTINCT zen_master_doc_id as master_doc_id, Applymap('TKT_MID2CreatedDateMap', zen_master_doc_id,Null()) as EventDate, 'tkt_created_date' as EventDateType
    Resident TICKETS;
    Load DISTINCT zen_master_doc_id as master_doc_id, Applymap('TKT_MID2SolvedDateMap', zen_master_doc_id,Null()) as EventDate, 'tkt_solved_date' as EventDateType
    Resident TICKETS;
// Drop Tables EU_MID2OrderDateMap, RES_MID2OrderDateMap, EU_MID2MaintEndDateMap, RES_MID2MaintEndDateMap, TKT_MID2CreatedDateMap, TKT_MID2SolvedDateMap;
15 Replies
vvira1316
Specialist II
Specialist II

Hi Samantha,

How about following....

MultiFact.JPG

NoConcatenate

Tickets:

LOAD zen_master_doc_id as master_doc_id,

     ticket_id,

     ticket_open_date,

     ticket_solved_date,

     language,

     country,

     company_name,

     region

FROM

TICKETS.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NoConcatenate

TransactionTmp:

LOAD eu_master_doc_id,

     res_master_doc_id,

     order_no,

     sku,

     order_dt,

     maint_start_dt,

     maint_end_dt,

     trans_region,

     revenue_type,

     res_name,

     res_city,

     res_geo,

     eu_name,

     eu_city,

     license_no,

     eu_geo,

     lic_type,

     qty,

     bookings_local,

     bookings_usd,

     acv_usd,

     acv_local

FROM

TRANSACTIONS.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NoConcatenate

Txn_Master:

LOAD

     eu_master_doc_id as master_doc_id,

     eu_master_doc_id & Chr(59) & res_master_doc_id  as combine_master_doc_id,

     'EU Master' as 'doc_type'

Resident TransactionTmp;

Concatenate

LOAD

     res_master_doc_id as master_doc_id,

     eu_master_doc_id & Chr(59) & res_master_doc_id  as combine_master_doc_id,

     'RES Master' as 'doc_type'

Resident TransactionTmp;

NoConcatenate

Transactions:

LOAD eu_master_doc_id & Chr(59) & res_master_doc_id as combine_master_doc_id,

     eu_master_doc_id,

     res_master_doc_id,

     order_no,

     sku,

     order_dt,

     maint_start_dt,

     maint_end_dt,

     trans_region,

     revenue_type,

     res_name,

     res_city,

     res_geo,

     eu_name,

     eu_city,

     license_no,

     eu_geo,

     lic_type,

     qty,

     bookings_local,

     bookings_usd,

     acv_usd,

     acv_local

Resident TransactionTmp;

DateTmp:

LOAD

min([ticket_open_date]) as min_ticket_open_date,

max([ticket_open_date]) as max_ticket_open_date,

min([ticket_solved_date]) as min_ticket_solved_date,

max([ticket_solved_date]) as max_ticket_solved_date

RESIDENT Tickets;

LET vMin_ticket_open_date = peek('min_ticket_open_date');

LET vMax_ticket_open_date = peek('max_ticket_open_date');

LET vMin_ticket_solved_date = peek('min_ticket_solved_date');

LET vMax_ticket_solved_date = peek('max_ticket_solved_date');

Drop Table DateTmp;

DateTmp:

LOAD

min([order_dt]) as min_order_dt,

max([order_dt]) as max_order_dt,

min([maint_start_dt]) as min_maint_start_dt,

max([maint_start_dt]) as max_maint_start_dt,

min([maint_end_dt]) as min_maint_end_dt,

max([maint_end_dt]) as max_maint_end_dt

RESIDENT TransactionTmp;

LET vMin_order_dt = peek('min_order_dt');

LET vMax_order_dt = peek('max_order_dt');

LET vMin_maint_start_dt = peek('min_maint_start_dt');

LET vMax_maint_start_dt = peek('max_maint_start_dt');

LET vMin_maint_end_dt = peek('min_maint_end_dt');

LET vMax_maint_end_dt = peek('max_maint_end_dt');

Drop Table DateTmp;

DROP Table TransactionTmp;

LET vStart = $(vMin_ticket_open_date);

LET vEnd = $(vMax_ticket_open_date);

IF $(vStart) > $(vMin_ticket_solved_date) then

LET vStart = $(vMin_ticket_solved_date);

ENDIF;

IF $(vStart) > $(vMin_order_dt) then

LET vStart = $(vMin_order_dt);

IF $(vStart) > $(vMin_maint_start_dt) then

LET vStart = $(vMin_maint_start_dt);

IF $(vStart) > $(vMin_maint_end_dt) then

LET vStart = $(vMin_maint_end_dt);

ENDIF;

ENDIF;

ENDIF;

IF $(vEnd) < $(vMax_ticket_solved_date) then

LET vEnd = $(vMax_ticket_solved_date);

ENDIF;

IF $(vEnd) < $(vMax_order_dt) then

LET vEnd = $(vMax_order_dt);

IF $(vEnd) < $(vMax_maint_start_dt) then

LET vEnd = $(vMax_maint_start_dt);

IF $(vEnd) < $(vMax_maint_end_dt) then

LET vEnd = $(vMax_maint_end_dt);

ENDIF;

ENDIF;

ENDIF;

LET vStart = floor(YearStart($(vStart)));

LET vEnd = floor(YearEnd($(vEnd)));

LET NumOfDays = $(vEnd) - $(vStart) + 1;

Date_src:

LOAD

$(vStart) + Rowno() - 1 as DateID

AUTOGENERATE $(NumOfDays);

[MasterCalendar]:

LOAD

DateID as order_dt, // just in case

date(DateID) as CalDate, // it will be in format defined in your SET DateFormat=, or in your system format

day(DateID) as CalDay,

week(DateID) as CalWeek,

month(DateID) as CalMonth, // simple month name; it is dual - numeric and text

dual(month(DateID) & '-' & year(DateID),

year(DateID) & num(month(DateID), '00')) as CalMonthYear, // Month-Year format, dual

year(DateID) as CalYear,

weekday(DateID) as CalWeekday,

'Q' & ceil(month(DateID)/3) as CalQuarter, // in format Q1, Q2, Q3, Q4

year(DateID) & ceil(month(DateID)/3) as CalQtrYear // Qn-Year, dual

// and whatever else you may want here...

RESIDENT Date_src;

Drop Table Date_src;

Anonymous
Not applicable
Author

Hi Vijay,

I have tried to get the number of tickets raised by the enduser on the given data model. I am getting the duplicate values. Please find the below screen shot for your reference:

Expression: Count({<doc_type={'EU Master'}>}ticket_id)

Untitled.jpg

Thanks,

Samanth

vvira1316
Specialist II
Specialist II

Hi Samantha,

Ticket Open Date(TOD) year is not same as Order Date, So what should be count for each of those order date year and quarter? How should TOD and Order date(Year, Qtr) be matched?

BR,

Vijay

Anonymous
Not applicable
Author

Hi Vijay,

Yes, Ticket Open Date is not same as Order Date.

But, still I want to know:

#Tickets raised by the enduser whether or not 'FY of Ticket Open Date' matches with 'FY of Order Date'

$Bookings done by the enduser whether or not 'FY of Order Date' matches with 'FY of Ticket Open Date' or not

I want to compare both #Tickets and $Bookings in the same row with FY dimension.

Please let me know if you need more details.

Thanks,

Samanth

Anonymous
Not applicable
Author

Any updates please?

Thanks,

Samanth

vvira1316
Specialist II
Specialist II

Hi Samantha,

As Ticket Open Date, Order Date Year are different there will be 0 ticket count for corresponding years. May be we can verify using a better data set.

BR,

Vijay