topic Combine multiple data sets without using synthetic keys in QlikView

Combine multiple data sets without using synthetic keys

Thu, 03 Jan 2013 10:29:47 GMT

Hi, I am new to Qlikview and have some basic questions.

I am trying to import a number of data sets into Qlikview and combine them.

The data sets are as follows.

App1

AuthenticatedUsername, PagesTitles, PagesURLs, Hits, PageViews, ViewTime, TimePeriod, StartDate, EndDate, App1 as Apptyp

App2

AuthenticatedUsername, PagesTitles, PagesURLs, Hits, PageViews, ViewTime, TimePeriod, StartDate, EndDate, App2 as Apptyp

App3

AuthenticatedUsername, PagesTitles, PagesURLs, Hits, PageViews, ViewTime, TimePeriod, StartDate, EndDate, App3 as Apptyp

All Apps

Apptyp, AuthenticatedUsername, Hits, PageViews, Visits, TimePeriod, StartDate, EndDate;

Enrichment Table

ReportingRegion, ReportingBusinessSector, ReportingFullName, AuthenticatedUsername

I would like the end result to be as follows..

The ability to filter on Apptyp and date then drilldown by ReportingRegion, ReportingBusinessSector, ReportingFullName, AuthenticatedUsername. This will then show what pages have been seen in which app depending on the selections made in the filters and drilldown.

I keep getting synthetic keys and have been playing around with concatenating tables, link tables etc however as I am new to Qlikview it would be good if someone could point me in the correct direction. There is a lot more data I want to add so hopefully after I crack this I will be on my way!

Thanks

Re: Combine multiple data sets without using synthetic keys

nirav_bhimani — Thu, 03 Jan 2013 10:34:06 GMT

Hi,

Please share your qvw file so that its more clear.

Regards,

Nirav Bhimani

Re: Combine multiple data sets without using synthetic keys

sreenivas — Thu, 03 Jan 2013 10:34:32 GMT

can u add Qvw file

Re: Combine multiple data sets without using synthetic keys

Thu, 03 Jan 2013 10:38:50 GMT

The data I deal with is quite sensitive so I would not be able to provide the Qvw file with the raw data in.

Re: Combine multiple data sets without using synthetic keys

sreenivas — Thu, 03 Jan 2013 10:41:06 GMT

atleast table structure of all tables

Re: Combine multiple data sets without using synthetic keys

Thu, 03 Jan 2013 11:00:24 GMT

I only need the visits data from the All Apps table.

The All Apps table will give me the visits which is session based for each user against each app, it is not possible to get correct visit data when measuring on a page level as App1 / App2 / App3 tables do.

Re: Combine multiple data sets without using synthetic keys

Thu, 03 Jan 2013 11:10:39 GMT

Hi danielfordwa,

Can you share a qvw file with dummy data,for say 10 lines from each table.

So that the scenario is more clear.

Regards,

Snehal Nabar

Re: Combine multiple data sets without using synthetic keys

Thu, 03 Jan 2013 11:28:20 GMT

Hello please try this,

At the start of the script type QUALIFY *; and immediately after this statement type UNQUALIFY Key field;

This qualifies all the attributes with the table names avoiding synthetic keys and then you can rename them as required.

Hope this helps.

Re: Combine multiple data sets without using synthetic keys

Thu, 03 Jan 2013 17:01:17 GMT

To add a bit more detail I am connecting to a database using ODBC and pulling out various reports.

To give an example of the data I have created some excel sheets with dummy data. See Attached. Each excel sheet is an example of the data received after each query made using OBDC.

Hope you can help!

Re: Combine multiple data sets without using synthetic keys

Thu, 03 Jan 2013 17:01:54 GMT

and the rest

Re: Combine multiple data sets without using synthetic keys

Thu, 03 Jan 2013 17:02:28 GMT

and the some more

Re: Combine multiple data sets without using synthetic keys

sreenivas — Fri, 04 Jan 2013 05:13:33 GMT

Check this Updated file it may fulfill your requirement