Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 3 table all associated by a person id (SAP schema).
These three tables all have start and end dates indicating whether a record is current or closed.
My main person table has 1000 records. My address table has 995 and my CRB has 25.
Using set analysis as such…
=count( {$<
PersonTable_StartDate= {"<=$(vNowdate)"}
, PersonTable_EndDate = {">$(vNowdate)"}
// CRB Status
, CRB_BeginDate= {"<=$(vNowdate)"}
, CRB_EndDate = {">$(vNowdate)"}
// Address
, AddTable_BegDate = {"<=$(vNowdate)"}
, AddTable_EndDate = {">$(vNowdate)"}
>} DISTINCT PersonId )
Now ideally I would have liked an open record in all of the tables and then this wouldn't present a problem. However, the CRB restriction before the Address restriction shows addresses of only those who have CRB records.
Question is… how do I modify my set expression so I end up with Nulls in the results for CRB so I get all the Addresses and also all the people regardless of whether they have an address record.
Hi,
Sorry for late response,
As I said You can Nullify fields, It means When You pass your set analysis
CRB_BeginDate= {"<=$(vNowdate)"}
CRB_EndDate = {">$(vNowdate)"}
Your PersonId will counted which available between your date but if So that Other PersonId will not be available for further query
but if You nullify that field value will available for further use.
But this will be wrong logic with respect to your expression,
Instead of this if Separately evaluate every expression you will get correct answer.
Like
count( {$<
PersonTable_StartDate= {"<=$(vNowdate)"}
, PersonTable_EndDate = {">$(vNowdate)"}
>} DISTINCT PersonId )+
// CRB Status
count( {$<CRB_BeginDate= {"<=$(vNowdate)"}
, CRB_EndDate = {">$(vNowdate)"}
>} DISTINCT PersonId )+
// Address
count( {$< AddTable_BegDate = {"<=$(vNowdate)"}
, AddTable_EndDate = {">$(vNowdate)"}
>} DISTINCT PersonId )
Regards,
Hi,
You can nullify your field in your set analysis so that CRB restriction will no affect your desired result.
Regards,
can you expand please?
Hi,
Sorry for late response,
As I said You can Nullify fields, It means When You pass your set analysis
CRB_BeginDate= {"<=$(vNowdate)"}
CRB_EndDate = {">$(vNowdate)"}
Your PersonId will counted which available between your date but if So that Other PersonId will not be available for further query
but if You nullify that field value will available for further use.
But this will be wrong logic with respect to your expression,
Instead of this if Separately evaluate every expression you will get correct answer.
Like
count( {$<
PersonTable_StartDate= {"<=$(vNowdate)"}
, PersonTable_EndDate = {">$(vNowdate)"}
>} DISTINCT PersonId )+
// CRB Status
count( {$<CRB_BeginDate= {"<=$(vNowdate)"}
, CRB_EndDate = {">$(vNowdate)"}
>} DISTINCT PersonId )+
// Address
count( {$< AddTable_BegDate = {"<=$(vNowdate)"}
, AddTable_EndDate = {">$(vNowdate)"}
>} DISTINCT PersonId )
Regards,
no, afraid that doesn't work as it acts as a sum (sums the two tables together so as the example below my total employee headcount becomes 1,254) and therefore inflates my numbers.
the PersonTable is like the fact table, with the other tables such as CRB hanging off it. if PersonTable has 1000 employees then this needs to be the constant total.
if 254 employees have CRB's:
with CRB: 254
Without CRB: 746
Total: 1,000
*bump*