How to search ECID creation date | Community
Skip to main content
M
Mia-jm_Wa
Level 2
November 15, 2024
Question

How to search ECID creation date

  • November 15, 2024
  • 1 reply
  • 805 views

Hi

We'd like to know the current profiles(have ECID but without OCMDID), how many of them created the profile within October?

What's the query to search

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

DavidRoss91
Community Advisor
DavidRoss91Community Advisor
Community Advisor
November 15, 2024

@mia-jm_wa 

You could try adjusting the "Single Identity Metric" that is provided in the Profile Overview tab - I have adjusted here for 10/1 - 10/31

SELECT
qsaccel.profile_agg.adwh_dim_namespaces.namespace_description,
SUM(qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.count_of_Single_Identity_profiles) AS count_of_Single_Identity_profiles
FROM
qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines
LEFT OUTER JOIN
qsaccel.profile_agg.adwh_dim_namespaces
ON qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE
qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = 227808421
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key BETWEEN '2024-10-01' AND '2024-10-31'
GROUP BY
qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id,
qsaccel.profile_agg.adwh_dim_namespaces.namespace_description;
    M
    Mia-jm_WaAuthor
    Level 2
    November 19, 2024

    Thanks for your reply.

    but below date_key is the total amount of single profile created by the day,  not the amount only on the day...

    qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key BETWEEN '2024-10-01' AND '2024-10-31'

     

    I would like the query to see the counts of profiles that contain only the ECID as an identity and were created in a particular month

      DavidRoss91
      Community Advisor
      DavidRoss91Community Advisor
      Community Advisor
      November 19, 2024

      @mia-jm_wa Sorry, I'm not sure I understand the difference. This query is pulling in ECID only by the day. But totaling all days in October as you asked. Does this not provide the value you need?

        Terms & ConditionsAccessibility statement

        Loading footer...