3 Replies Latest reply: Apr 3, 2013 9:03 AM by Stefan Wühl RSS

    Problem with Data from different sources.

      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.
          Stefan Wühl

          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.

              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.
                  Stefan Wühl

                  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