topic Re: Need a left join in set analysis please. in QlikView

Need a left join in set analysis please.

wonkymeister — Mon, 24 Mar 2014 08:31:29 GMT

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.

Re: Need a left join in set analysis please.

PrashantSangle — Mon, 24 Mar 2014 08:54:28 GMT

Hi,

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

Regards,

Re: Need a left join in set analysis please.

wonkymeister — Mon, 24 Mar 2014 09:13:48 GMT

can you expand please?

Re: Need a left join in set analysis please.

PrashantSangle — Mon, 24 Mar 2014 09:47:56 GMT

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.

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,

Re: Need a left join in set analysis please.

wonkymeister — Mon, 24 Mar 2014 10:24:52 GMT

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

Re: Need a left join in set analysis please.

wonkymeister — Tue, 25 Mar 2014 09:57:51 GMT

*bump*