In between counts for dates

[MK9885]

Hello,

I've a requirement to count a record by range based.

Ex:

Fact:

EMP     StartDate     EMPname    Status    

01       02/01/2014     ABC             Available

01       03/01/2017     BCD             Offline

In above scenario the fact has 2 entry for EMP01 for 02/2014 and 03/2017

So if I do counts it will come as 2.

But he is available through out 02/2014 till 03/2017.

So if I select 01/2015 or 05/2016 he should still be counted in that month.

My Fact is linked to master based on Datekey which is 20140201 and 20170301

I need to calculate based on range and also count him from 2014, 2015, 2016 and 2017 till March 01.

If I select any year or any month in between 2014 and 2017 I should get an entry, which I'm not getting now.

Can someone please help me calculating range based?

stalwar1‌ neelamsaroha1575‌

Thanks.

[neelamsaroha157]

In my script I have created Calendar using 'Effective Date' so that's why I had to load it first, then create a calendar from that, then load EMP.

So for sequence, Calendar should be generated when you do interval join for Start & end dates because it would be using date generated from calendar, that's all. 

[MK9885]

Understood, Master Calendar is sorted but in my EMP table I'm getting error..

EMP:

.....................

From............

EMP_Final:

Load*,

Min(E_EffectiveDate) as STDATE

Max(E_ExpiryDate) as ENDDT

Resident EMP

Group By EMPLOYEEKEY,;

DROP Table EMP;

left Join( EMP_Final)

IntervalMatch( [Effective Date])

LOAD Distinct STDATE  ,

ENDDT 

Resident EMP_Final;

I get a invalid expression error which might be due to Group By statement...

I've 20+ fields in EMP table.. few are numerics and other are alphabets.

[neelamsaroha157]

It is because Group By statement requires all the fields to be mentioned in the clause that you have in Load statement.

So instead of using *, use only EMPLOYEEKEY.

[MK9885]

At first I used EMPLOYEEKEY only but it said field names must be unique.

If I change the name maybe I've to rename in fact as well....?

EDIT:

Now it says Table not found, this requirement is really frustrating.

EDIT2:

The Table is Loading now for EMP... have to see how the load goes for other tables including fact

If I don't use *, then the left join I'm doing in fact for some other field is not working cus no field found.

I got to use *..... and if I use * I get Invalid expression

[neelamsaroha157]

One thing you can do is -- Keep your Original EMP, Take EMPID, StartDate & EndDate and find min & max of these.

then do a left join of this table back to your EMP table.

Something like this;

load EMPID,

       Field1,

      Filed2,

      Field3

      StartDate,

     EndDate

From

EMP Table;

left join(EMP)

Load EMPID,

        Min(StartDate),

       Max(EndDate)

resident EMP

group by EMPID;

[MK9885]

I tried, the script ran successfully but the nothing happened.

I can send you my script ?

Mainly 3 tables are involved in this...

EMP, Master and Fact

the sequencing for them is also in similar way...

First EMP loads, then Master and Fact.

I cannot load EMP after Fact cus I'm doing a left join in Master to make a composite key.

Yesterday I tried another way but it throws me an error

though the script seem to be correct but the records it's fetching is 76 million rows

EMP_tmp:

Load

  EMPLOYEEKEY,

  min(E_ROWEFFECTIVEDTS) as E_ROWEFFECTIVEDTS_B,

    max(E_ROWEXPIRATIONDTS) as E_ROWEXPIRATIONDTS_B

Resident EMP

Group By EMPLOYEEKEY;

tmp_BridgeTable1:

IntervalMatch([Effective Date_tmp])

LOAD E_ROWEFFECTIVEDTS_B, E_ROWEXPIRATIONDTS_B Resident EMP_tmp;

DROP Table EMP_tmp;

tmp_BridgeTable2:

LOAD

  *,

  trim(date([Effective Date_tmp],'YYYYMMDD')) as DATEKEY

Resident tmp_BridgeTable1;

BridgeTable:

Left Join(tmp_BridgeTable2)

LOAD DATEKEY, F_EMPLOYEEKEY as EMPLOYEEKEY Resident Fact;

DROP Table tmp_BridgeTable2;

DROP Field [Effective Date_tmp] From MasterCalendar;

The above script is loaded after everything is loaded... this is in last tab.

[neelamsaroha157]

EMP_tmp:

Load

  EMPLOYEEKEY,

  min(E_ROWEFFECTIVEDTS) as E_ROWEFFECTIVEDTS_B,

    max(E_ROWEXPIRATIONDTS) as E_ROWEXPIRATIONDTS_B

Resident EMP

Group By EMPLOYEEKEY;

left Join(EMP_tmp)

IntervalMatch([Effective Date_tmp])

LOAD E_ROWEFFECTIVEDTS_B, E_ROWEXPIRATIONDTS_B Resident EMP_tmp;

DROP Table EMP_tmp;

After this you can do a left join to your main EMP table on the basis of EMPID.

Once you are done with this you'll get one final EMP table with all your fields of main table + mindate field+maxadate field+effectivedatefield.

Then you can start working on building the link table.

I am not sure if you can link on the combination of DateKey & EMPID, you'll need a link table.