Peek only showing first value

[#1]

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;

[MayilVahanan]

HI

Can you able to attach sample data in ACTIVE_SEGMENTS file.

[#3]

ACTIVE_SEGMENTS attached

thanks

[MayilVahanan · Accepted solution]

Hi

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'); 

[#5]

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

[swuehl]

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...