filling in empty fields

[DianaK]

Hi! Help me solve the problem pls. I need to add the state and county values base on the field (nn). the original table looks like this

te	ab	cd	name	nn
110000000	11	00	New York state	00
111000000	11	10	Erie County	01
111010000	11	10	Buffalo
111033000	11	10	Harborcreek
111033100	11	10	Fairview
111033300	11	10	Millcreek
111800000	11	18	Essex County	02
111810000	11	18	Chesterfield
111849100	11	18	Minerva
111851000	11	18	Moriah
111851100	11	18	Jay
113200000	11	32	Dutchess County	03
113220000	11	32	Amenia
113220100	11	32	Beekman
113220200	11	32	Clinton
150000000	15	00	Texas state	00
151000000	15	10	Anderson County	01
151010000	15	10	Palestine
151011100	15	10	Elkhart
151013100	15	10	Frankston
153200000	15	32	Angelina County	02
153220000	15	32	Burke
153220100	15	32	Diboll

And  I nedd to end up with a table like this

te	ab	cd	name	nn	state name	county name
110000000	11	00	New York state	00	New York state	-
111000000	11	10	Erie County	01	New York state	Erie County
111010000	11	10	Buffalo		New York state	Erie County
111033000	11	10	Harborcreek		New York state	Erie County
111033100	11	10	Fairview		New York state	Erie County
111033300	11	10	Millcreek		New York state	Erie County
111800000	11	18	Essex County	02	New York state	Essex County
111810000	11	18	Chesterfield		New York state	Essex County
111849100	11	18	Minerva		New York state	Essex County
111851000	11	18	Moriah		New York state	Essex County
111851100	11	18	Jay		New York state	Essex County
113200000	11	32	Dutchess County	03	New York state	Dutchess County
113220000	11	32	Amenia		New York state	Dutchess County
113220100	11	32	Beekman		New York state	Dutchess County
113220200	11	32	Clinton		New York state	Dutchess County
150000000	15	00	Texas state	00	Texas state	-
151000000	15	10	Anderson County	01	Texas state	Anderson County
151010000	15	10	Palestine		Texas state	Anderson County
151011100	15	10	Elkhart		Texas state	Anderson County
151013100	15	10	Frankston		Texas state	Anderson County
153200000	15	32	Angelina County	02	Texas state	Angelina County
153220000	15	32	Burke		Texas state	Angelina County
153220100	15	32	Diboll		Texas state	Angelina County

I tried to do it like this, but it didn't work

Index:
LOAD
te,
ab,
cd,
name,
nn
FROM [lib://dataacademy/katonew2.xls]
(biff, embedded labels, table is katonew2$);

Left Join
Load

nn
if(isNull(nn) and nn=00, Peek('StateName'), name) as [StateName],
if((nn)>1, Peek('CountyName'),name) as [CountyName]
Resident [Index];

[MarcoWedel · Accepted solution]

table1:
LOAD RecNo() as ID,
     te, 
     ab, 
     cd, 
     name, 
     nn,
     If(nn='00', name, Peek(StateName)) as StateName,
     If(Len(Trim(nn)) and nn<>'00', name, If(nn<>'00',Peek(CountyName))) as CountyName
FROM [https://community.qlik.com/t5/QlikView-App-Dev/filling-in-empty-fields/td-p/1885426] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

Marco

[DianaK]

thank you! it works!!!