Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
wonkymeister
Contributor

Need a left join in set analysis please.

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.

1 Solution

Accepted Solutions

Re: Need a left join in set analysis 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,

Great dreamer's dreams never fulfilled, they are always transcended.
5 Replies

Re: Need a left join in set analysis please.

Hi,

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
wonkymeister
Contributor

Re: Need a left join in set analysis please.

can you expand please?

Re: Need a left join in set analysis 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,

Great dreamer's dreams never fulfilled, they are always transcended.
wonkymeister
Contributor

Re: Need a left join in set analysis please.

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
Contributor

Re: Need a left join in set analysis please.

*bump*

Community Browser