Rows below a value

[adnan_rafiq]

Hi Experts, I would like your help on this situation. See the table below

A	B
ABC
DEF
HIJ
HGT
LMN
OPQ

I want to create a column B in the above table, which looks at A and sees value "DEF" and puts the value 100 in all the rows that are below DEF until it sees OPQ, so new table looks like this

                            a                                                                                                  b

ABC
DEF
HIJ	100
HGT	100
LMN	100
OPQ
IUJ

[swuehl · Accepted solution]

Maybe like

LOAD A,

     If(previous(A) = 'DEF', 100, if(A = 'OPQ',NULL(),Peek('B'))) as B

FROM

[https://community.qlik.com/thread/213397]

(html, codepage is 1252, embedded labels, table is @1);

[marcus_sommer]

You could with interrecord-functions like above() look into other rows and columns. Something like this should cover your requirement:

if(above(a) ='DEF' or (above(b) = 100 and not a = 'OPQ'), 100, '')

- Marcus

[Kushal_Chawda]

Data:

LOAD A,

FROM Table;

Final:

noconcatenate

LOAD A,

          if(previous(A)='DEF',100,if(A='OPQ','',peek('B'))) as B

Resident Data

order by A;

drop table Data;

[adnan_rafiq]

is above allowed in load scripting.

[adnan_rafiq]

Just 2 other subsequent questions swuehl, what if i want to create and other column (Column C) and if a value in Column A comes again in Column a I write in Column C as repeated.

secondly is it possible to limit the load to a certain value in Column, lets say only load until value OPQ

[swuehl]

You could resolve the first request using Exists() function:

LOAD A,

    If(previous(A) = 'DEF', 100, if(A = 'OPQ',NULL(),Peek('B'))) as B,

    If(Exists(A),'Repeated') as C

FROM

[https://community.qlik.com/thread/213397]

(html, codepage is 1252, embedded labels, table is @1);

[Note that in this scenario, field A should not have been loaded before, like a key field. If it does, you may need to work with a copy of field A]

W.r.t. your second question, try something like

LOAD A,

    If(previous(A) = 'DEF', 100, if(A = 'OPQ',NULL(),Peek('B'))) as B,

    If(Exists(A),'Repeated') as C

FROM

[https://community.qlik.com/thread/213397]

(html, codepage is 1252, embedded labels, table is @1)

WHERE Peek('A') <> 'OPQ';

[adnan_rafiq]

Thanks swuehl‌, you are great I wanted to give u more points for correct 2nd answer, but I don't know how?. May be I can ask you again?

Thanks a lot anyway

[swuehl]

You are welcome.

Here is a description of how you can flag answers as correct or helpful:

Qlik Community Tip: Marking Replies as Correct or Helpful