2 Different Date Data Sets

Grecs5
Grecs5 ⚪️
edited March 3 in Card Building

I have a problem where I need to match up 2 different data sets to show funnel. Create Date & Demo Date. I need to show this data by week number. A demo date can be weeks after the create date. Here is my example:

Data set 1

Week Create Count

1 10

2 20


Data Set 2

Week Demo Count

1 3

2 8


How can I merge these data sets do I can get matching numbers as shown below:

Week Create Demo

1 10 3

2 20 8

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🔵

    You need to join these two datasets together to make one dataset. The easiest way to do this is with Magic ETL. The below example shows you more than just joining, but the idea is that you can use the join tile to join your two datasets together. You would join on week. You might want to review this KB article to better understand the different types of joins if you are unfamiliar with the concept.



  • GrantSmith
    GrantSmith Indiana 🔵

    Hi @Grecs5

    alternatively you can use a data set view to join your two datasets together.


    another option to think about might be instead of joining your data together you UNION / stack your data together such that you have

    TYPE | WEEK | COUNT

    Demo | 1 | 3

    Create | 1 | 10

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔵

    Sorry @GrantSmith and @MarkSnodgrass hard disagree.


    Don't JOIN the data. APPEND them.

    If you get to the point where you have multiple rows per week OR if dataset 1 is at a different granularity than dataset2 then you end up with row growth (because you're JOINING) and you have to crazy math to 'SUM DISTINCT).


    1) add a constant "Activity Type" to each dataset so you know what type of metric you're dealing with

    2) duplicate the date column and call it "Activity Date" for both datasets. (then use Activity Date in Analyzer).

    3) UNION the data

    https://www.youtube.com/watch?v=PVbOeLSae9o&t=772s

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!