topic Re: Loop skips the duplicate line in QlikView

Loop skips the duplicate line

Wed, 02 Sep 2015 08:53:59 GMT

Hi QV gurus,

I have a problem with looping: when a field has duplicate, the loop skips the duplicate line. For example:

TABLE:
load * inline
[A, B, C
China, Cotton, Shirt
India, Cotton, Socks
Japan, Leather, Jacket];

let vNOR=NoOfRows('TABLE');
for i=1 to $(vNOR)
let vA=FieldValue('A',$(i));
let vB=FieldValue('B',$(i));
let vC=FieldValue('C',$(i));

trace $(vA);
trace $(vB);
trace $(vC);

NEXT;

You can see in column B, line 1 and line 2 has duplicate values. When I run the script, the log is as below:

TABLE << INLFFD1 3 lines fetched
China
Cotton
Shirt
India
Leather
Socks
Japan

Jacket

The record "India-Cotton-Socks" became "India-Leather-Socks" while for Japan, the column B is missing. So I guess the loop skips the duplicates in column B, thus causing a problem with the data.

Could you kindly teach me how to fix this?

BR, PQ

Re: Loop skips the duplicate line

fernando_tonial — Wed, 02 Sep 2015 17:07:48 GMT

Hi, You can try this.

You have use a function Peek() insted FieldValue().

TABLE:
load * inline 
[A, B, C
China, Cotton, Shirt
India, Cotton, Socks
Japan, Leather, Jacket];


let vNOR=NoOfRows('TABLE');
for i=1 to $(vNOR)
let vA=Peek('A',$(i)-1);
let vB=Peek('B',$(i)-1);
let vC=Peek('C',$(i)-1);


trace $(vA);
trace $(vB);
trace $(vC);


NEXT;

Best Regards.

Don't Worry, be Qlik.

Tonial.

Re: Loop skips the duplicate line

sasiparupudi1 — Wed, 02 Sep 2015 19:11:51 GMT

this is the help text

FieldValue(fieldname , n )

Returns the field value found in position n of the field fieldname (by load order). fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes. The first field value is returned for n=1. If n is larger than the number of field values, NULL is returned.

Note: This function will only work with distinct field values.

use Peek

TABLE:

load * inline

[A, B, C

China, Cotton, Shirt

India, Cotton, Socks

Japan, Leather, Jacket];

let vNOR=NoOfRows('TABLE');

for i=0 to $(vNOR)-1

let vA=Peek('A',$(i));

let vB=Peek('B',$(i));

let vC=Peek('C',$(i));

trace $(vA);

trace $(vB);

trace $(vC);

NEXT;

Re: Loop skips the duplicate line

maxgro — Wed, 02 Sep 2015 19:37:05 GMT

TABLE:

load * inline

[A, B, C

China, Cotton, Shirt

India, Cotton, Socks

Japan, Leather, Jacket];

for i=0 to NoOfRows('TABLE')-1

let vA=Peek('A',$(i));

let vB=Peek('B',$(i));

let vC=Peek('C',$(i));

trace $(vA) $(vB) $(vC);

NEXT;

TABLE << INLE08 3 lines fetched

China Cotton Shirt

India Cotton Socks

Japan Leather Jacket