Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have below two tables. Table1 and Table2. After I loaded the data, Qlikview auto joined the tables using the key.
Table1
Key | Column1 |
SN1 | A |
SN2 | B |
SN3 | C |
SN4 | D |
Table2
Key | Column2 |
SN1 | A1 |
SN2 | B1 |
SN3 | |
SN4 | D1 |
Qlikview joined the table using the key.
Key | Column1 | Column2 |
SN1 | A | A1 |
SN2 | B | B1 |
SN3 | C | |
SN4 | D | D1 |
Question here - If I want the null field in Column2 replaced with the value "C" in Column1. How can I replace the value and achieve below result?
Column1 | Column2 | |
SN1 | A | A1 |
SN2 | B | B1 |
SN3 | C | C |
SN4 | D | D1 |
Thank you.
tmp: Directory; LOAD Key, Column1 FROM Book1.xlsx (ooxml, embedded labels, table is Sheet1); Left Join (tmp) LOAD Key, Column2 FROM Book1.xlsx (ooxml, embedded labels, table is Sheet2); NoConcatenate tmp2: LOAD Key ,Column1 ,If(Len(Trim(Column2)) = 0, Column1, Column2) as Column2 Resident tmp; DROP Table tmp;
And the results:
Pay attention that you will be left with only one table.
tmp: Directory; LOAD Key, Column1 FROM Book1.xlsx (ooxml, embedded labels, table is Sheet1); Left Join (tmp) LOAD Key, Column2 FROM Book1.xlsx (ooxml, embedded labels, table is Sheet2); NoConcatenate tmp2: LOAD Key ,Column1 ,If(Len(Trim(Column2)) = 0, Column1, Column2) as Column2 Resident tmp; DROP Table tmp;
And the results:
Pay attention that you will be left with only one table.