topic Re: Rows below a value in QlikView

Rows below a value

adnan_rafiq — Fri, 15 Apr 2016 10:53:57 GMT

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

Re: Rows below a value

swuehl — Fri, 15 Apr 2016 10:59:51 GMT

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

marcus_sommer — Fri, 15 Apr 2016 11:00:22 GMT

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

Kushal_Chawda — Fri, 15 Apr 2016 11:10:17 GMT

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;

Re: Rows below a value

adnan_rafiq — Sat, 16 Apr 2016 02:22:26 GMT

is above allowed in load scripting.

Re: Rows below a value

adnan_rafiq — Sat, 16 Apr 2016 02:28:25 GMT

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

Re: Rows below a value

swuehl — Sat, 16 Apr 2016 10:41:00 GMT

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

Re: Rows below a value

adnan_rafiq — Sun, 17 Apr 2016 01:16:54 GMT

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

Re: Rows below a value

swuehl — Sun, 17 Apr 2016 10:35:41 GMT

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