Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

simple QV load statement help please...

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

4 Replies
johnw
Champion III
Champion III

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
;

Not applicable
Author

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
Champion III
Champion III

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.

Not applicable
Author

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

Much thanks, John, as usual 🙂