Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
adnan_rafiq
Partner
Partner

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

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

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

Kush
MVP
MVP

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

is above allowed in load scripting.

adnan_rafiq
Partner
Partner
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
Partner
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