Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
adnan_rafiq
Contributor 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
Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Rows below a value

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

8 Replies
MVP
MVP

Re: Rows below a value

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

Re: Rows below a value

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

Re: Rows below a value

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
Contributor II

Re: Rows below a value

is above allowed in load scripting.

adnan_rafiq
Contributor II

Re: Rows below a value

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

MVP
MVP

Re: Rows below a value

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
Contributor II

Re: Rows below a value

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

MVP
MVP

Re: Rows below a value

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

Community Browser