topic Re: self join in QlikView

self join

Wed, 01 Jul 2015 09:17:55 GMT

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.

Re: self join

PrashantSangle — Wed, 01 Jul 2015 09:26:45 GMT

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

Re: self join

Wed, 01 Jul 2015 09:39:38 GMT

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.

Re: self join

PrashantSangle — Wed, 01 Jul 2015 09:48:52 GMT

Hi,

try below

BusinessDates_Final:

load

%BusinessDate as %BusinessDate,

%BusinessDate-1 as PreviousBusDate,

BusinessDates.Rowid as %Rowid

Resident BusinessDates;

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

Re: self join

Wed, 01 Jul 2015 10:36:57 GMT

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.

Re: self join

PrashantSangle — Wed, 01 Jul 2015 10:41:21 GMT

Hi,

Use second suggestion provided in above reply use Previous()

Regards

Re: self join

prashantbaste — Wed, 01 Jul 2015 10:54:02 GMT

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

Re: self join

Wed, 01 Jul 2015 11:48:25 GMT

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

Re: self join

Wed, 01 Jul 2015 11:49:59 GMT

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