Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic intervals between rows

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

1values
1values
2values
A
B
1values
2values
2values
B
C
1values
2values
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!

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

PFA

Not applicable
Author

Thank you very much, Massimo. Very useful!

MarcoWedel

Hi,

another one:

QlikCommunity_Thread_142445_Pic1.JPG.jpg

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

Not applicable
Author

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?