Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adnan_rafiq
Partner - Creator II
Partner - Creator II

Rows below a value

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

AB
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
HIJ100
HGT100
LMN100
OPQ
IUJ
1 Solution

Accepted Solutions
swuehl
MVP
MVP

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);

View solution in original post

8 Replies
swuehl
MVP
MVP

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
Partner - Creator II
Partner - Creator II
Author

is above allowed in load scripting.

adnan_rafiq
Partner - Creator II
Partner - Creator II
Author

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

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
Partner - Creator II
Partner - Creator II
Author

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

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