Load values in every 3rd row of a field

[#1]

Hallo QV geniuses,

I have a sample data like this:

Test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

How can I load every 3rd row in the Test field? So the output will be like this:

Test
3
6
9
12
15

I want to do it inside the script editor so that the whole QV document is limited. The reason why I do this because I want to create a demo document by not putting all data.

Best,

Reagan

[maxgro]

t:

load * inline [

field

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

];

t1:

NoConcatenate load * Resident t where Mod(RecNo(),3)=0;

DROP Table t;

[joshabbott]

If you want the numbers 3, 6, 9, 12, 15, you could do this which I ran in a test app:

test1:

LOAD * INLINE [
test1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
];

test2:
LOAD test1 as test2
Resident test1
WHERE mod(test1,3) = 0;

If they aren't numbers 3, 6...etc, then you could load your data with a new column (rowno()) like this:

test1:

LOAD * INLINE [
test1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
];

test2:
LOAD rowno() as testrowno, test1 as test2
Resident test1;

test3:
LOAD testrowno, test2 as test3
resident test2
WHERE mod(testrowno,3) = 0;


drop table test1;
drop table test2;

[#4]

Thanks all but the problem is I have a total of 1500 rows. I don't think inline is good with a lot of rows. I want every 3rd row loaded.

[jaimeaguilar]

Hi,

Maybe like this,

check the example please

regards

[#6]

Hi jaime,

Thanks for the idea.

How can I substitute my field with your example? You use autogenerate which I feel not needed because I already have a set of data with 1500 rows. 

I have a field name "UID" with 1500 rows. So how can I do it using your example?

[maxgro · Accepted solution]

I used inline because I don't have your data (qvd or something else)

load

     *

from

     ...yourfile.......

where Mod(RecNo(),3)=0;

example

t: load  rowno() as UID autogenerate 1500;

t1: NoConcatenate load * Resident t where mod(RecNo(),3)=0;

DROP Table t;

[jaimeaguilar]

Hi,

Just change the autogenerate for your table, In my example I used 2 tables (the original autogenerated and the filtered). In your case it can be like this (using just one table):

YourUnfilteredTable:

Load UID, //Load any other field you need

        RecNo() as Counter

from SourceTable //Here type the path for your source table (xls, db, etc)

Where Even(RecNo())+1 and Recno() <> 1;

then if you don't need the Counter field you can drop it using: Drop Field Counter;

if you want to validate data it will be better that first load both tables like in the example.

Also if your field UID is a numbered and unique field you can use that field instead of the RecNo() then it would be like this (just make sure your data is well sorted):

YourUnfilteredTable:

Load UID, //Load any other field you need

from SourceTable //Here type the path for your source table (xls, db, etc)

Where Even(UID)+1 and UID <> 1;

Also note that Recno() / Rowno() begin in zero. If your UID begins counting from 1 then this part is not necessary: and UID <> 1

Try to adapt the example to your code, if still not working it will be better if you could upload a sample of your data,

regards