topic Re: Problem with Data from different sources. in QlikView

Problem with Data from different sources.

Tue, 02 Apr 2013 13:46:03 GMT

Hey everyone, just in advance. thanks so much for helping before. Now i have another Problem.

I now found the matching of data problem to be solved. Now when i want to add two sources i have a problem with the following code;

LOAD

order_id,

email,

shipping_method,

total as order_amount,

order_status_id,

payment_method,

date_added,

Date(Date('YYYY-MM-DD'),'DD.MM.YYYY') as date_final,

Year(Date(Date(Mid(date_added,1,10),'YYYY-MM-DD'),'DD.MM.YYYY')) as year,

Month(Date(Date(Mid(date_added,1,10),'YYYY-MM-DD'),'DD.MM.YYYY')) as month,

Week(Date(Date(Mid(date_added,1,10),'YYYY-MM-DD'),'DD.MM.YYYY')) as week;

SELECT

order_id,

email,

shipping_method,

total,

order_status_id,

payment_method,

date_added

from live.order;

GoogleAnalyticsConnectorV2_Data:

LOAD

dim_date as dim_date,

Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY') as date_final,

Year(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY')) as year,

Week(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY')) as week,

Month(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY')) as month,

dim_medium as medium,

dim_keyword as keyword,

dim_source as source,

metric_visits as visits,

dim_landingPagePath as landingpage,

metric_visitors as unique_visitor

FROM

[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV2/?table=Data&appID=&feed=https%3a%2f%2fwww.google.com%2fanalytics%2ffeeds%2fdata%3fids%3dga%XXX%26dimensions%3dga%3adate%2cga%3amedium%2cga%3asource%2cga%3alandingPagePath%2cga%3akeyword%26metrics%3dga%3avisitors%2cga%3avisits%26start-date%3d2012-01-01%26end-date%3d2013-03-31%26max-results%3d10000&format=html]

(html, utf8, embedded labels, table is @1);

What happens now in german called "Zirkelbezüge" appear, rougly translated with " circle relations". How can help?

Best

Christoph

Re: Problem with Data from different sources.

swuehl — Tue, 02 Apr 2013 13:55:57 GMT

Christoph,

I assume you are having at least one more table ("Zirkelbezüge" needs at least 3 tables in your data model).

Without knowing your complete "Zirkelbezug", it's hard to suggest something.

There is a whole chapter in the reference manual, search for "Zirkelbezüge vermeiden".

A solution to your problem probably involves renaming some fields or re-modelling your data model (e.g. using 1 single fact table instead of multiple, using common date fields).

Re: Problem with Data from different sources.

Tue, 02 Apr 2013 18:30:40 GMT

Hey,

indeed i have three different tables with data formats which seem to cause the Zirkelbezüge.

The problem is:

1. one order table which provides all relevant data to the order. -> matching through date

2. one table which delivers traffic data from google -> matching through date

3. one table which contains costs for adwords -> matching throug date

When i rename the date fields from google, everything works fine. otherwise its screwing up my systems. With the datefields its actually working when i just use two sources from the one database, its only google that is messing with the data.

Re: Problem with Data from different sources.

swuehl — Wed, 03 Apr 2013 13:03:10 GMT

Christoph,

you'll need to decide how you want to interact with your data, how you want to filter your data.

For example, do you want to select a date and force QV to limit the original fields dim_date as well as date_added to that selected date, or are these dates semantically different and you may want to select different dates in these fields.

There are a lot of threads here in the forum (and in the technical blog) covering the concept of a so called master calendar, and how to link a master calendar to several date fields (using a link table) or how to build separate master calendar tables per date field.

For example, please check:

http://community.qlik.com/message/273830#273830