I have a query as:
select count(distinct sfdc_account_id), from account union select count(distinct account_id), from fct_acco
I want to know if both counts from above query are same or not? How to do this in matillion?
1
-
Maybe a Query Result To Scalar followed by an If component. If the counts are the same your union query will return 1 row. If they are different it will return 253epo– 53epo2024-07-18 07:10:26 +00:00Commented Jul 18, 2024 at 7:10
Add a comment
|
1 Answer
I wanted to share an answer to your question as there are a few options to achieve this.
There are several ways to achieve this, I have shared some below for you to read over and check out:
Set Up Data Sources:
Use the "Table Input" component to pull data from the account table. Use another "Table Input" component to pull data from the fct_acco table.
Aggregate the Counts:
For each Table Input component, use the "Aggregate" component to get the distinct counts. For the account table, configure the Aggregate component to count distinct sfdc_account_id. For the fct_acco table, configure the Aggregate component to count distinct account_id.
Join or Union the Results:
Since you want to compare the counts, you can use the "Calculator" component to create a single row for each count and then use a "Join" or "Union" component to bring the two counts together for comparison.
Comparison Logic:
Use the "Calculator" component to create a new column that indicates if the counts are equal. You might need a "Filter" component to filter based on this comparison if needed.
Calculator Component:
Add a column for the comparison result: CASE WHEN account.distinct_count = fct_acco.distinct_count THEN 'Equal' ELSE 'Not Equal' END AS comparison_result
I hope that helps, I look forward to hearing how you get on :)
