Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

Highlighted
Creator II
Creator II

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;

 

Highlighted

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