Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
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
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
thank you! it works!!!