simple QV load statement help please...

[#1]

I have this load:

First_Business_Days:

noconcatenate load bq_date as fb_date, RowNo() as fb_row_pos

resident First_Business_days

where first_business = -1;

it ends up with a list of dates (first business days of each week) and the row number in the table

'2009-01-19', 1

'2009-01-26', 2

'2009-02-02', 3

etc

What I need is a result set that has:

date, row number, earlier-date that exists 18 rows up (earlier in the timeline)

so the 20th row would have:

date, 20, {whatever date was at row-number-2}

It's easy as hell in SQL, but QV load syntax has me flummoxed

Thanks,

Bob

[johnw]

I'm guessing this sort of join is what you meant by doing it easily in SQL?

First_Business_Days:
NOCONCATENATE
LOAD
bq_date as fb_date
,rowno() as fb_row_pos
RESIDENT First_Business_days
WHERE first_business = -1
;
LEFT JOIN (First_Business_Days)
LOAD
fb_row_pos - 18 as fb_row_pos
,fb_date as earlier_date
RESIDENT First_Business_Days
;

Probably even easier using peek?

First_Business_Days:
NOCONCATENATE
LOAD
bq_date as fb_date
,rowno() as fb_row_pos
,peek(fb_date,-18) as earlier_date
RESIDENT First_Business_days
WHERE first_business = -1
;

[#3]

Heh, nice shot John, if I change the - to a + sign, I get the results I want... except for the empty values for the first 18 rows of "earlier_date". ?Is there a way to set them to, say, min(fb_date) or something like it? (I would assume any function like min or max would force me to use a group by, etc...

[johnw · Accepted solution]

Well, I assumed you wanted null for the first 18 rows. If you want the very first date, '2009-01-19' for your example data, I'm guessing this?

First_Business_Days:
NOCONCATENATE
LOAD
bq_date as fb_date
,rowno() as fb_row_pos
,if(rowno()= 1, bq_date
,if(rowno()<19,peek(fb_date,0)
,peek(fb_date,-18))) as earlier_date
RESIDENT First_Business_days
WHERE first_business = -1
;

I didn't think of any easy way to do it with the join approach, but I wouldn't use the join approach anyway.

[#5]

Wow, bang on. I need to learn about peek, I think.

Much thanks, John, as usual 🙂