self join

[#1]

Hi,

I am trying a self join on a table like this:

BusinessDates_Final:

load  

  %BusinessDate as %BusinessDate,

  BusinessDates.Rowid as %Rowid 

Resident BusinessDates;

left JOIN (BusinessDates)

load  

  %BusinessDate as %PreviousBusinessDate,

  BusinessDates.Rowid - 1 as %Rowid 

Resident BusinessDates;

I am basically trying to use a table of dates  then self join on the Rowid   which is incremental by date, so i get a table BusinessDates_Final which contains %BusinessDate,  %Rowid  , %PreviousBusinessDate,. The above script runs but I only get 2 columns on the  %BusinessDate,  %Rowid . Why is this not working producing 3?

Thanks.

[PrashantSangle]

Hi,

are you left join it to BusinessDates or BusinessDates_Final??

If you left join it to BusinessDates and used it in BusinessDates_Final.

then write the script before BusinessDates_Final.

try like

left JOIN (BusinessDates)

load  

  %BusinessDate as %PreviousBusinessDate,

  BusinessDates.Rowid - 1 as %Rowid 

Resident BusinessDates;

BusinessDates_Final:

load  

  %BusinessDate as %BusinessDate,

  BusinessDates.Rowid as %Rowid 

Resident BusinessDates;

Regards

[#3]

I thought it should just be Joinbut tried the Left just in case it works.  I tried your script it doesn't work, not sure if you missed something. Let me make it more clear then.

Table BusinessDates looks like this.

%BusinessDate, Rowid

01-01-2015          1

02-01-2015          2

03-01-2015          3

So i need a resulting table which looks like this

%BusinessDate, Rowid, PreviousBusDate

01-01-2015          1         

02-01-2015          2          01-01-2015

03-01-2015          3          02-01-2015

I am just trying to do a simple self join for BusinessDates table as per my example.

Thank you.

[PrashantSangle]

Hi,

try below

BusinessDates_Final:

load  

  %BusinessDate as %BusinessDate,

    %BusinessDate-1 as PreviousBusDate,

  BusinessDates.Rowid as %Rowid 

Resident BusinessDates;

or

you can use previous() also

try like

BusinessDates:
LOAD * INLINE [
    %BusinessDate, RowId
    01-01-2015, 1
    02-01-2015, 2
    03-01-2015, 3
];

BusinessDates_Final:

load  

  %BusinessDate as %BusinessDate,

    previous(%BusinessDate) as PreviousBusDate,

  RowId as %Rowid 

Resident BusinessDates;

DROP Tables BusinessDates;

Regards

[#5]

Hi Max,

Thanks but this no good either I think. Need to use the RowId in the join. Imagine Monday and Friday, i can't just subtract -1 from the day to get the previous, or it could be Xmas holidays where on the 27Dec the previous working day is the 24 Dec. So i have to load the business days and then use the rowID to find the previous working day with the self join.

Thanks.

[PrashantSangle]

Hi,

Use second suggestion provided in above reply use Previous()

Regards

[prashantbaste]

Hi Panlondin

Try this -

BusinessDates_Final:

LOAD  %BusinessDate as %BusinessDate,

            BusinessDates.Rowid as %Rowid

Resident BusinessDates;

LEFT JOIN

LOAD  %BusinessDate as %PreviousBusinessDate,

          BusinessDates.Rowid - 1 as %Rowid

Resident BusinessDates;

Let me know if this resolves your issue or I can help more...

--

Regards,

Prashant P Baste

[#8]

That doesn't work as it wouldn't know the previous bus date. It needs to have the id somewhere in the link.

[#9]

previous() is answer, although i will still like to see an answer with a join on Rowid! Thanks everyone!

BusinessDates:
LOAD * INLINE [
    %BusinessDate, RowId
    01-01-2015, 1
    02-01-2015, 2
    03-01-2015, 3
];

BusinessDates_Final:

load  

  %BusinessDate as %BusinessDate,

    previous(%BusinessDate) as PreviousBusDate,

  RowId as %Rowid 

Resident BusinessDates;

DROP Tables BusinessDates