Dynamic intervals between rows

[#1]

Hi everyone!

Searching for a little help.

I'm working on dynamic input files with lot of rows.

The source file is a fixed Excel (that means I can't modify the input out of Qlik) and in a simple way it could looks like this:

A
1	values
1	values
2	values
A
B
1	values
2	values
2	values
B
C
1	values
2	values
C

I'm searching a criterium to label values, to map the dynamic intervals in which they lie.

Intervals' lenght are not the same along the rows, and they also vary on different inputs.

I tought about assign a variable to min occurrence and max occurrence of "A" in the first column, and then tag the values inside with another criterium (in this case, if first column =1 they should be labeled in the same category, if =2 in another category: in the example above, all values are in their own category except row2&row3 and  row8&row9),

but input file is long enough to discourage me set each single variable for min/max and the make lot of "where" in the script.

Is there a shortest way to create something like a map that manage intervals?

Thank you!

[Gysbert_Wassenaar]

If the data in your excel file is ordered as in the example you posted you can try:

Data:

LOAD * WHERE len(trim(values))>0;

LOAD

     FirstColumn,

     values,

     if( len(trim(values))>0 , peek('Label'), FirstColumn) as Label

FROM ...excel_file...

[#3]

Thank you Gysbert for your answer!

Unfortunately my example was poorly appropriate so the solution is not correct.

In the attachment there's an extract of the first two column of the input file (marked yellow).

My goal is to obtain the pointer as in column 3 (marked green), as you see just on the rows where values in column B starts with a code.

[maxgro · Accepted solution]

PFA

[#5]

Thank you very much, Massimo. Very useful!

[MarcoWedel]

Hi,

another one:

LOAD *,
    If(IsNum(SubField(@2,' ',1)),'Category'&Cat) as Category;
LOAD *, RecNo() as ID,
    RangeSum(Peek(Cat),-(not IsNum(SubField(Previous(@2),' ',1)) and IsNum(SubField(@2,' ',1)))) as Cat
FROM [http://community.qlik.com/servlet/JiveServlet/download/658551-137793/lug_ex.xls] (biff, no labels, table is Foglio1$);

hope this helps

regards

Marco

[#7]

Thank you Marco...

I raise... and if I want to create a second map from @1 values?

So along with the logic you defined, add another field with something dynamic like

from first occurrence of "TESTO" to last occurrence of "TESTO" assign value 1, from first to last occurrence of "ATT.B)" assign value 2? Is it possibile?