Need a left join in set analysis please.

[wonkymeister]

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.

[PrashantSangle]

Hi,

You can nullify your field in your set analysis so that CRB restriction will no affect your desired result.

Regards,

[wonkymeister]

can you expand please?

[PrashantSangle · Accepted solution]

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,

[wonkymeister]

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

[wonkymeister]

*bump*