Yes my flag is a 0 or a one its based on a sql that determines if someone applied for same job group in the last six months, a 1 means they didn't apply and 0 means they did. So I am trying to get a count of all the employees that applied to same job group

I have developed my rpd exactly like you described in your response. I have dimensions and fact joined

I understand you can copy the logical sql and run it in the underlying database but when I try it I get an error. could that explain my problem?

a Flag is a measure and I am breaking it down by employee number. But for some reason the same query is giving me the desired results in the database but same cant be said in analysis.

I wrote the sql to test my counts and I wasn't expecting to use same sql on the front end. My question was how I could do something similar to get the correct results. in this case I know J_GROUP, PI_COMP_DT and CAN_NUM are in right granuality and I can get the right count if I do distinct count and then group by…

ACT_CAND is my measure and its a count based on J_GROUP, PI_COMP_DT, CAN_NUM

How do you do this part For the fact you simply need to adjust the fact column to correspond to the combination of columns you need to do a COUNT(DISTINCT ) on. You set "count distinct" as aggregation and done

the reason I am doing distinct count is because there are a lot of duplicates, so that takes care of it. I tried the count and I am not getting correct count. I have modeled my rpd all I want to know is how can I get a count with a group by without duplicates in either rpd or analysis.

My thought process about using filtering was not going any where so I tried a different logic. sorry if I caused any confusion

I finally figured it out. using this (sum(count (distinct "Fact - XXY"."# Applicants" by "XXY"."Job ")) )

No, I am looking for something like this FILTER(.... USING ....)

yes I am talking about FILTER(.... USING ....) function? can we use group by with this function? if so do you have an example?

The grin changed because I added some additional columns to capture information that was not available when the original table was created. Basically when I added the new column it causes the above flag to repeat itself in multiple rows since the column I am adding has multiple rows.

A_CAN is a flag, its either 1 or 0. so the sum basically give me the sum of 1's in a region or by job. and Yes the risk to the model discussed and approved by management

Let me back up a little bit to explain whats happening : Originally This (A_CAN) is the flag that is using the sum , changed the fact table to capture some additional information and by doing that causes this flag to have repeating values, to get the count correctly and eliminate duplicate I had done two steps - 1 ran this…

Correct but the problem is once I find the interns, I need to find out who was an intern that got hired to a full time position . This is the reason I wanted to have an INTERNS table, then try figure out how to find out who was hired to full time position because there is data indicator in the warehouse that shows someone…

I plan to create an ODI data flow to refresh the table regularly which is what is happening on the current tables.

I am executing SQL in the DB and using DbaaS. I didn't use the type-2 SCD because I wasn't so sure how to come up with that script.

I came up with the following code to extract and create an Interns table, its capturing the Interns as expected and now the hard part is to create a table that will have a column showing a count of those interns. create table INTERNS (APPLICATION_DT,CANDIDATE_NUM,COMPLETED_DT,STATUS_NAME) AS SELECT…

I came up with the following code to extract and create an Interns table, its capturing the Interns as expected and now the hard part is to create a table that will have a column showing a count of those interns. create table INTERNS (APPLICATION_DT,CANDIDATE_NUM,COMPLETED_DT,STATUS_NAME) AS SELECT…