topic Re: Data model in QlikView

Data model

deepakqlikview_123 — Thu, 06 Mar 2014 08:27:42 GMT

Hello Expert,

PFA.

Can you please suggest how to build data model for this.

Thanks,

Deepak

Re: Data model

puttemans — Thu, 06 Mar 2014 08:51:29 GMT

Hi,

You need to find the common links first, and make sure there is only 1. I guess that customer and customer ID will contain the same information, so by renaming one of them similar to the other in the load statement, you create a link within QV. If you only have 1 campaign every month, then the start field of the campaign may be linked with the month field in clicks. If that is not the case, then you need to look further into your data (I don't know them). If you do not find a good connector, then you may need to create one first if that's possible.

Regards,

Re: Data model

Thu, 06 Mar 2014 08:52:55 GMT

upload a sample of each table and I will tell you

Cheers,

Byron

Re: Data model

vikasmahajan — Thu, 06 Mar 2014 08:58:51 GMT

Hii

Please find sample application will help you build data model.

Vikas

Re: Data model

deepakqlikview_123 — Thu, 06 Mar 2014 16:54:53 GMT

Hello Bryn

Cliks table

EmailOpenTime	Customer id	Month	Year	Clicked
12/29/2012 12:00	1	12	2012	Clicked
2/2/2013 12:00	2	2	2013	No Click
3/2/2013 7:00	3	3	2013	Clicked
12/25/2012 16:15	4	12	2012	Clicked
12/26/2012 16:15	5	12	2012	Clicked
12/27/2012 16:15	6	12	2012	No Click
12/28/2012 16:15	7	12	2012	Clicked
12/29/2012 16:15	8	12	2012	Clicked
12/30/2012 16:15	9	12	2012	No Click
12/31/2012 16:15	10	12	2012	Clicked
1/1/2013 16:15	11	1	2013	Clicked
1/2/2013 16:15	12	1	2013	Clicked
1/3/2013 16:15	13	1	2013	Clicked
1/4/2013 16:15	14	1	2013	Clicked
1/5/201316:15	15	1	2013	No Click
1/6/2013 16:15	16	1	2013	Clicked
3/7/2013 16:15	17	3	2013	Clicked
1/8/2013 16:15	18	1	2013	No Click
1/9/2013 16:15	19	1	2013	Clicked

campaign table

Campaign id	Start	End	Name	Cost
1	12/25/2012	1/2/2013	XMAS	50K
2	1/1/2013	1/29/2013	JAN SALES	75k

Customer table

Customer	Age	Country	Gender
1	24	UK	Male
2	33	France	Female
3	17	Germany	Male
4	58	UK	Male
5	53	UK	Female
6	33	UK	Male
7	55	Germany	Male
8	42	UK	Female
9	44	UK	Male
10	45	UK	Male
11	51	UK	Female
12	22	UK	Male
13	24	UK	Male
14	36	Germany	Female
15	50	UK	Male
16	12	UK	Male
17	30	UK	Female
18	15	UK	Male
19	12	UK	Male
20	17	Germany	Female

can you please send me screenshot for data model I do not have licenced version,

thanks

Deepak

Re: Data model

Thu, 06 Mar 2014 17:51:13 GMT

Paste this code in your load script after you have done a load of the original tables

/***********************************************************************************

Step 1: Cleanup and Primary Key

***********************************************************************************/

CampaignFinal:

NoConcatenate

Load

"Campaign id" as CampaignID,

Alt(Date#(Start,'MM/DD/YYYY'),Start) as Start,

Alt(Date#(End,'MM/DD/YYYY'),End) as End,

Capitalize(Lower(Name)) as Name, //Cleaner look

if(Right(Upper(Cost),1)='K',num(PurgeChar(Upper(Cost),'K'))*1000,num(Cost)) as Cost //Proper numberic formatting, assumed there is either a 1000 indicator i.e. k or straight value

Resident Campaign;

Drop Table Campaign;

CliksFinal:

Load

CustomerID,

Month,

Year,

EmailOpenTime,

Date(Floor(EmailOpenTime)) as EmailOpenDate;

Load

"Customer id" as CustomerID, //"Customer id" and Customer in Customers table are the same

Month,

Year,

Alt(Date#(EmailOpenTime,'MM/DD/YYYY hh:mm'),EmailOpenTime) as EmailOpenTime

Resident Cliks;

Drop Table Cliks;

CustomersFinal:

NoConcatenate

Load

Customer as CustomerID, //Customer and "Customer id" in Cliks table are the same

Age,

if(Country='UK','United Kingdom',Capitalize(Lower(Country))) as Country, //EU will now see a clean list box of full country name

Capitalize(Lower(Gender)) as Gender //To ensure same text formatting

Resident Customers;

Drop Table Customers;

/***********************************************************************************

Final Step: Join Interval Match Table

***********************************************************************************/

IntervalMatch (EmailOpenTime) // Create an interval link table to EmailOpenTime

JOIN (CampaignFinal)

StartEndDate:

Load

Start, End

Resident CampaignFinal;

exit script;

Re: Re: Data model

Thu, 06 Mar 2014 17:55:26 GMT

For those that don't have personal edition, here is the qvw

Re: Re: Data model

deepakqlikview_123 — Thu, 06 Mar 2014 18:11:21 GMT

Hi Byron,

Thanks it is very useful for me

Can u plz tell me what to do in below scenario

An online marketing team sends out two email campaigns to customers using their CRM system. The first is their Christmas campaign and the second is the New Year sales campaign.

You are asked to deliver an interactive dashboard to help the CEO analyse the performance of the email marketing campaign over Christmas and the New Year sales.

thanks

Re: Data model

Thu, 06 Mar 2014 18:14:34 GMT

Load this script in you application.

Click:

LOAD Date(EmailOpenTime) as EmailOpenTime,

[Customer id] as Customer,

Month,

Year,

Clicked

FROM

C:\Users\windows\Desktop\QVDS\Click.qvd

(qvd);

Campaign:

LOAD [Campaign id],

Start,

End ,

Name,

Cost

FROM

C:\Users\windows\Desktop\QVDS\Campaign.qvd

(qvd)

where [Campaign id] <> '' ;;

Join

IntervalMatch (EmailOpenTime)

LOAD Start,

End as CampingEndDate

Resident Campaign;

Customer:

LOAD Customer,

Age,

Country,

Gender

FROM

C:\Users\windows\Desktop\QVDS\Customers.qvd

(qvd);

Re: Re: Data model

Thu, 06 Mar 2014 18:17:19 GMT

Hi Deepak,

Its a pleasure. If the above solution is the right one, please mark it correct so others know as well.

With regards to your next quesiton, once again you need data which im sure they provided in the question. The question is quite simple; compare performance for the different campaigns.

Since the data will most probably share exactly the same fields with the exception that there should be a field that indicates which campaign, you would concatenate

Concatenate(Christmas)

LOAD *

Resident NewYear;

...or along those lines

Cheers,

Byron

Re: Re: Data model

deepakqlikview_123 — Thu, 06 Mar 2014 18:26:20 GMT

for making comparison bar chart is enough is it.

Re: Re: Data model

deepakqlikview_123 — Thu, 06 Mar 2014 18:28:50 GMT

this works

Re: Re: Data model

Thu, 06 Mar 2014 18:29:21 GMT

I would say yes, but you must have both dimensions in your chart i.e. date and campaign

in the presentation tab, set the bar distance at 0 so they are next to each other. Makes it easier to distinguish what the trend is and against what campaign

Re: Data model

deepakqlikview_123 — Fri, 07 Mar 2014 04:20:28 GMT

Hi,

Can u please tell me how to perform comparison for above scenario in below case,

The first is their Christmas campaign and the second is the New Year sales campaign.

You are asked to deliver an interactive dashboard to help the CEO analyse the performance of the email marketing campaign over Christmas and the New Year sales.

Thanks,

Re: Data model

Fri, 07 Mar 2014 06:52:54 GMT

This question has been answered. Open a new question or search the forums