Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
richnorris
Creator II
Creator II

Find every FieldIndex of a value?

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:

StuffFooBar
Adultsstuffstuff
Mumstuffstuff
Dadstuffstuff
Subtotalstuff
Childrenstuffstuff
Peterstuffstuff
Marystuffstuff
Subtotalstuff
stuff
stuff
Friendsstuffstuff
Mikestuffstuff
Subtotalstuffstuff

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?

2 Replies
boorgura
Specialist
Specialist

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;

NEXT

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

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

Hope to help!

MarcoWedel

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:

QlikCommunity_Thread_306272_Pic1.JPG

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