topic Re: Dynamic intervals between rows in QlikView

Dynamic intervals between rows

Mon, 17 Nov 2014 16:37:57 GMT

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!

Re: Dynamic intervals between rows

Gysbert_Wassenaar — Mon, 17 Nov 2014 17:35:15 GMT

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

Re: Re: Dynamic intervals between rows

Mon, 17 Nov 2014 18:37:54 GMT

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.

Re: Dynamic intervals between rows

maxgro — Mon, 17 Nov 2014 19:44:56 GMT

PFA

Re: Dynamic intervals between rows

Mon, 17 Nov 2014 22:23:33 GMT

Thank you very much, Massimo. Very useful!

Re: Dynamic intervals between rows

MarcoWedel — Mon, 17 Nov 2014 23:29:20 GMT

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

Re: Dynamic intervals between rows

Wed, 19 Nov 2014 17:22:25 GMT

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?