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: 
mrichman
Creator II
Creator 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
sunny_talwar

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

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;

 

sunny_talwar

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