topic Find every FieldIndex of a value? in QlikView

Find every FieldIndex of a value?

richnorris — Wed, 27 Jun 2018 19:18:50 GMT

Maybe there's a better way to do what I'm doing, but I have a spreadsheet that I want to pull all the data from, but its broken down into sections that I want to load into separate tables:

Stuff	Foo	Bar
Adults	stuff	stuff
Mum	stuff	stuff
Dad	stuff	stuff
Subtotal		stuff
Children	stuff	stuff
Peter	stuff	stuff
Mary	stuff	stuff
Subtotal		stuff
		stuff
		stuff
Friends	stuff	stuff
Mike	stuff	stuff
Subtotal	stuff	stuff

So, I want to load from Adults to Subtotal, then Children to Subtotal, then Friends to Subtotal, each time into a different table. I'm going to be doing a LOT of this, so iterating over it programatically is really the way to go, but also it has to be dynamic (From X to Y rows loaded each time) because it can be the case that rows get added or removed. You'll notice there are differing spaces between 'Subtotal' rows and the next relevant part of info. My plan was to load from the table on an iterator with a pick on the words "Adults, Children, Friends" etc the values for N (For each N in etc etc) and use

Let vStartRow = FieldIndex('Stuff', '$(i)')

Load * from table where RowNo() >= $(vStartRow)

but then I want to load until the Nth 'Subtotal' found. I can't seem to find a way to find say, the third instance of 'Subtotal' in a column? I guess there's probably a way I can do it with peek instead and just say keep going until you hit a subtotal, I'll probably do that. But, is there really no way to find Nth occurrence of a value?

Re: Find every FieldIndex of a value?

boorgura — Wed, 27 Jun 2018 20:17:23 GMT

I dont think there's one correct solution to this. One way to achieve it:

------------------

Test:

LOAD *, RowNo() as row_no;

LOAD * Inline [

Stuff,Foo,Bar

Adults,stuff,stuff

Mum,stuff,stuff

Dad,stuff,stuff

Subtotal,,stuff

Children,stuff,stuff

Peter,stuff,stuff

Mary,stuff,stuff

Subtotal,,stuff

Friends,stuff,stuff

Mike,stuff,stuff

Subtotal,stuff,stuff

];

Breakrows:

NoConcatenate

LOAD row_no-1 as breakpoint

Resident Test where Stuff = 'Subtotal';

LET vTableCount = NoOfRows('Breakrows');

LET i =1;

LET vPrevBreak = 0;

FOR i = 0 to $(vTableCount)-1

LET vBreakPoint = peek('breakpoint', $(i), 'Breakrows');

TRACE $(vBreakPoint);

QUALIFY *;

Table:

NoConcatenate

LOAD * Resident Test

where

row_no <= $(vBreakPoint)+1 and row_no > $(vPrevBreak)

;

UNQUALIFY * ;

LET vPrevBreak = $(vBreakPoint)+1;

---------------------------------------------------------

As you wanted each of them into a separate table - I used NOCONCATENATE and QUALIFY within the loop.

Hope to help!

Re: Find every FieldIndex of a value?

MarcoWedel — Wed, 27 Jun 2018 20:56:15 GMT

Hi,

I'm not quire sure about your separate table requirement but one solution might be to mark your sections while loading the source table subsequently splitting it into sections specific tables like this:

Source table with section markers:

mapStuffSections:
Mapping
LOAD *, RecNo()
INLINE [
    Section
    Adults
    Children
    Friends
];

table1:
LOAD RecNo() as ID,
    RangeSum(Peek(SectionNo),Sign(ApplyMap('mapStuffSections',Stuff))) as SectionNo,
    If(ApplyMap('mapStuffSections',Stuff),Stuff,Peek(Section)) as Section,
    If(ApplyMap('mapStuffSections',Stuff),1,If(Previous(Stuff)='Subtotal',0,Peek(IsInSection))) as IsInSection,
    Stuff,
    Foo,
    Bar
FROM [https://community.qlik.com/thread/306272] (html, codepage is 1252, embedded labels, table is @1);

FOR Each vSection in FieldValueList('Section')
   NoConcatenate
   tab$(vSection):
   LOAD *
   Resident table1
   Where Section = '$(vSection)' and IsInSection;
NEXT

DROP Table table1;

hope this helps

regards

Marco