topic Re: reading excel and ROWNO() / RECNO() in QlikView

reading excel and ROWNO() / RECNO()

amien — Thu, 20 Jun 2013 23:21:56 GMT

I have an excel file with for every week a sheet. I read all these sheets with a loop.

I only take 5 rows of each sheet. I take these 5 rows based on a certain WHERE clause. I only take the rows where a certain column is not empty.

The result will be a table with 5 rows for each week and a value.

Based on the filename i have the year. Based on the sheet name i have the week. Now i'm looking for the day to create a date.

Using MAKEWEEKDATE to create the date. the day is only causing an issue. the 5 rows that i read are always in the right order (mon,tue,wed etc).

But how do i create a number of that (0,1,2,..) . ROWNO and RECNO don't work. Also tried to create something with AUTONUMBER, but wasn't solving it

Anyone got a solution for this?

Re: reading excel and ROWNO() / RECNO()

Gysbert_Wassenaar — Fri, 21 Jun 2013 06:26:13 GMT

Perhaps you can use the weekday function on the result of the makeweekdate to get the day of the week. That's a dual value so it can be sorted numerically (or you can use the num function on it to get the weekday number).

Re: reading excel and ROWNO() / RECNO()

amien — Fri, 21 Jun 2013 09:34:50 GMT

Gysbert, the problem is that i'm looking for the makeday. I don't have a valid date for makeday

MAKEWEEKDATE('2013','22','1')

MAKEWEEKDATE('2013','22','2')

MAKEWEEKDATE('2013','22','3')

MAKEWEEKDATE('2013','22','4')

MAKEWEEKDATE('2013','22','5')

2013 is based on the filename, 22 on sheetname, 1...5 on ??

what i basicly need is a row counter while looping though each sheet. A new sheet should reset the counter. I only read 5 rows on each sheet (reduced on WHERE clause). I was hoping ROWNO or RECNO would solve this, but this is not the case

perhaps i misunderstood you?

Re: reading excel and ROWNO() / RECNO()

Gysbert_Wassenaar — Fri, 21 Jun 2013 10:12:24 GMT

More likely I'm misunderstanding you . I can't see the files or data you have to work with. Maybe mod(recno(),5) gives you the numbers you need.

Re: reading excel and ROWNO() / RECNO()

swuehl — Fri, 21 Jun 2013 10:37:27 GMT

Try a preceding load:

LOAD *,

makeweekdate(Year, Week, recno() ) as Date;

LOAD Year, Week, Value FROM ... where ...;

Re: reading excel and ROWNO() / RECNO()

amien — Fri, 21 Jun 2013 10:52:30 GMT

i think the best solution is indeed a precedent load .. all the records are in the correct order.

the mod solution works almost but not in all cases (i have several excel files each with 52 weeks).

but the RECNO() with WHERE won't work? because i think the second monday will have a 6 instead of a 0.

can i do a counter in the preceding loading?

somethink like : IF(PREVIOUS(DayValue) = 5,0,PREVIOUS(DayValue)+1)

Only problem is that DayValue doesn't exist yet

Re: reading excel and ROWNO() / RECNO()

amien — Fri, 21 Jun 2013 16:01:22 GMT

i fixed it with a precent load, previous and peek

Thanks both