Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mrichman
Contributor II

How to get value of previous field or max/peek value of previous field with the same name (city)?

I have data but some are missing codes. I have tried Peek() and it has worked but not perfectly. Thanks in advance!

If Code is "NoCode" then take the Code above it with the same City, else take the max code or peek code (i'm not sure which one) for this specific row with the same City.

 

Data Sample: 

LOAD*Inline [
City, Town, Code
City1, Town1, 1000,
City1, Town2, 2000,
City1, Town3, NoCode,
City2, Town1, 1001,
City2, Town2, NoCode,
City2, Town3, NoCode,
City1, Town4, NoCode,
City1, Town5, NoCode
]

3 Replies

Re: How to get value of previous field or max/peek value of previous field with the same name (city)

May be try this

DataSample: 
LOAD * INLINE [
    City, Town, Code
    City1, Town1, 1000
    City1, Town2, 2000
    City1, Town3, NoCode
    City2, Town1, 1001
    City2, Town2, NoCode
    City2, Town3, NoCode
    City1, Town4, NoCode
    City1, Town5, NoCode
];

FinalTable:
NoConcatenate
LOAD City,
	 Town,
	 If(City = Previous(City), If(Code = 'NoCode', Peek('Code'), Code), Code) as Code
Resident DataSample
Order By City, Town;

DROP Table DataSample;

To get this

image.png

mrichman
Contributor II

Re: How to get value of previous field or max/peek value of previous field with the same name (city)

Hi Sunny,

Thanks for your prompt reply as always. After applying your example, I found out that a lot of my first rows were also empty (see example). I would like to give City1, Town1 the max Code possible with the same City, in this case that would be 2000.

DataSample:
LOAD * INLINE [
City, Town, Code
City1, Town1, NoCode <--------
City1, Town2, 2000
City1, Town3, NoCode
City2, Town1, 1001
City2, Town2, NoCode
City2, Town3, NoCode
City1, Town4, NoCode
City1, Town5, NoCode
];

FinalTable:
NoConcatenate
LOAD City,
Town,
If(City = Previous(City), If(Code = 'NoCode', Peek('Code'), Code), Code) as Code
Resident DataSample
Order By City, Town;

DROP Table DataSample;

 

Re: How to get value of previous field or max/peek value of previous field with the same name (city)

Let me ask you this... do you want to apply the max code or previous code? Those two are different things and I want to make sure that I give you a code which works for the issue you are trying to address