topic Re: Peek only showing first value in QlikView

Peek only showing first value

Thu, 28 Jan 2016 08:36:13 GMT

Hi,

I have some script that loads in a list of active segments and creates a list of filenames that we want to load. All of that stuff works fine.

Each time a file is loaded I also want to put the name of the segment in as a field.

The script works for the first file we load but for the subsequent loads the segment is blank. What am I doing wrong here with the variable vSeg ??

//-O_O-// Auto create the filenames based on the current reporting month

ACTIVE_SEGMENTS_0:

LOAD

SEGMENT,

OPERATIONAL,

$(vReportingYear) & $(vReportingMonthNumber) &'_'& SEGMENT &'_'& 'Party.txt' as FILENAME

FROM $(vSourceData)ACTIVE_SEGMENTS.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE OPERATIONAL = 1;

//-O_O-// Load the filenames that we want to loop through

ACTIVE_SEGMENTS:

LOAD

Concat(FILENAME, '|') as List,

Count(FILENAME) as Count

RESIDENT ACTIVE_SEGMENTS_0

WHERE OPERATIONAL = 1;

//DROP TABLE ACTIVE_SEGMENTS_0;

//-O_O-// Set up the loop

LET vList = Chr(39) & Peek('List') & Chr(39);

LET vCount = Peek('Count');

FOR i = 1 to $(vCount)

LET vFile = SubField($(vList), '|', $(i));

LET vSeg = Peek('SEGMENT',i,'ACTIVE_SEGMENTS_0');

//-O_O-// Start a blank table structure which we will then concatenate to later

Party:

LOAD * INLINE [ CUS_IDR, SEGMENT ];

//-O_O-// Concatenate the data

CONCATENATE(Party)

LOAD

distinct(CUS_IDR) as CUS_IDR,

'$(vSeg)' as SEGMENT

FROM

[$(vOperational)$(vFile)]

(txt, utf8, embedded labels, delimiter is '\t', no quotes, no eof);

//WHERE NOT EXISTS (CUS_IDR);

//-O_O-// Loop until we are finished

Next i;

//-O_O-// Tidy Up

DROP TABLE ACTIVE_SEGMENTS_0;

DROP TABLE ACTIVE_SEGMENTS;

Re: Peek only showing first value

MayilVahanan — Thu, 28 Jan 2016 10:35:18 GMT

Can you able to attach sample data in ACTIVE_SEGMENTS file.

Re: Peek only showing first value

Thu, 28 Jan 2016 11:12:23 GMT

ACTIVE_SEGMENTS attached

thanks

Re: Peek only showing first value

MayilVahanan — Thu, 28 Jan 2016 11:35:25 GMT

Try like this

//-O_O-// Auto create the filenames based on the current reporting month

ACTIVE_SEGMENTS_0:

LOAD

RowNo() as Rowno,

SEGMENT,

OPERATIONAL,

SEGMENT &'_'& 'Party.txt' as FILENAME

FROM ACTIVE_SEGMENTS.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE OPERATIONAL = 1;

//-O_O-// Load the filenames that we want to loop through

ACTIVE_SEGMENTS:

LOAD

Concat(FILENAME, '|', Rowno) as List,

Count(FILENAME) as Count

RESIDENT ACTIVE_SEGMENTS_0

WHERE OPERATIONAL = 1

Order by Rowno asc;

//DROP TABLE ACTIVE_SEGMENTS_0;

//-O_O-// Set up the loop

LET vList = Chr(39) & Peek('List') & Chr(39);

LET vCount = Peek('Count');

FOR i = 1 to $(vCount)

LET vFile = SubField($(vList), '|', $(i));

LET vSeg = Peek('SEGMENT',$(i)-1,'ACTIVE_SEGMENTS_0');

Re: Peek only showing first value

Thu, 28 Jan 2016 12:27:42 GMT

Hi,

Thanks very much for the solution - I got it to work.

Why does Peek jump forward one value resulting in having to do $(i)-1 ?

thanks

Stuart

Re: Peek only showing first value

swuehl — Thu, 28 Jan 2016 12:56:45 GMT

Why does Peek jump forward one value resulting in having to do $(i)-1 ?

That's because some QV functions (like Peek() ) start indexing by 0 (while some other functions, like Subfield() start by 1).

Indexing is one of the last creative spaces left for developers...