Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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;
HI,
Would it be possible for you to provide sample of those two tables, application and calculations you are trying to do. It will help us to help you.
I have a complex data model with more than one fact tables, look up tables, calendar, etc.. As can be seen from screen shot below. My calculations and data model has worked just fine. I've used it in multiple dashboards and for complex calculations.
Hi Samanth,
Is there a reason EU Doc Id and Res Doc Id are together in same table? Having both of them in same transaction table is making it difficult to tie it to tickets. Also if you were to split them up what data points will gets associated with each of them.
I just created two separate Transaction tables with all fields. If I were to leave eu_master doc_id in res_mst or res_master_doc_id in eu_mst along with actual transaction table then it would create a loop. A loop in data model will cause problems.
With data model I've got you will/may have to use set analysis for certain calculations.
Please see attached QVW file.
Hi Vijay,
Thank you very much for your valuable time spending on this.
Q: Is there a reason EU Doc Id and Res Doc Id are together in same table?
Ans: The only reason is: Both EU and RES are having the common data like order_no, order_dt, bookings_usd, acv_local etc…
There is no problem even if we separate them into two tables by renaming the columns.
I have tested the data model that you have given. It looks very nice.
I tried to get some report by adding date calendar to date fields. But, I am getting the duplicated data like displayed in below report.
Report:
- Dimensions:
- master_doc_id
- Fiscal Year : fiscal year of ticket_created_date or order_dt
- Fiscal Quarter : fiscal quarter of ticket_created_date or order_dt
- Measures:
- #EU Tickets : number of tickets raised by enduser => count of ticket_id
- #RES Tickets : number of tickets raised by reseller => count of ticket_id
- $ EU Bookings : total bookings by enduser => sum of bookings_usd
- $ RES Bookings : total bookings by reseller => sum of bookings_usd
Can you please help me in getting the above report? Based on that, I can derive any other reports.
If we are able to get this kind of report that would be great!
Thanks & Regards,
Samanth