1 Reply Latest reply on Jul 6, 2018 10:35 AM by Yuriy Fal

    Merge Different Date Dimensions across Multiple Sources from Excel and Bigquery

    Volodymyr Diakonov

      Hello,
      I have 2 data sources - 3 datasets from Bigquery and Excel file. I have 4 dimensions.
      For example, 'a' in which there are values for each day and 'b' in which also there are values, etc. Looks like this

       

      a (excel source)
      ---
      userid          date
      aa1            2018-06-27 12:16:52.000

      bb1            2018-06-27 11:11:52.000

      aa1            2018-06-26 09:16:52.000

      bb1            2018-06-26 18:16:52.000

      cc1            2018-06-25 18:16:52.000

       

      b (Bigquery source)
      ---
      userid          date
      aa1            2018-06-27 19:16:52.000

      bb1            2018-06-27 01:11:52.000

      aa1            2018-06-26 03:16:52.000

      cc1            2018-06-27 18:16:52.000

      bb1            2018-06-26 15:16:52.000

       

      c (Bigquery source)
      ---
      userid          date
      aa1            2018-06-27 19:16:52.000

      bb1            2018-06-27 01:11:52.000

      aa1            2018-06-26 03:16:52.000

      cc1            2018-06-27 18:16:52.000

      bb1            2018-06-26 15:16:52.000

       

      d (Bigquery source)
      ---
      userid          date
      aa1            2018-06-27 19:16:52.000

      bb1            2018-06-27 01:11:52.000

      aa1            2018-06-26 03:16:52.000

      cc1            2018-06-27 18:16:52.000

      bb1            2018-06-26 15:16:52.000

       

       

      At first I made JOIN tables by userid (like this) http://joxi.net/RmzoyVkS0vLd92

       

      I have to create line graphic to show user dynamics per day - I made count distinct users and convert it to measure and that was okay. After that I had 4 measures for each dataset and discover changes of distinct amount of users per day in each dataset. Further I have to calculate coefficients between a/b distinct users, a/c distinct users and a/d distinct users in general. That was okay - cause I've created new measures and divide one measure to another.  Like this COUNTD([userId (a)]) / COUNTD([userId (b)])

      But after that i need to see change of coefficients by days. I can't solve this problem because none of two dates ('date' from 'a' and 'date' from 'b') does not working. http://joxi.ru/a2XnNGQC1Qk5dr

       

      Did somebody have similar difficulties in solving problem merge dates from different sources for aggregate measures??