topic Re: Search for equal field in other column in QlikView

Search for equal field in other column

buc_christian — Fri, 10 Apr 2015 21:36:26 GMT

Hallo Community,

i have a Scripting Problem and am searching for help.

my data structure looks like that:

Column A, Column B, Column C

1, 0, 99

2, 1, 999

3, 4, 9

4, 6, 9999

5, 2, 99999

within the script I want to:

Load

Column A,

"search for value in of column A in column B and return the value of column C" as Value

From xxx;

The Table in my App should look like:

Column A, Value

1, 999

2, 99999

Thanks for any help in advance!!

Chris

Re: Search for equal field in other column

Anonymous — Fri, 10 Apr 2015 21:51:52 GMT

Chris, I'd do it by mapping. First, create map:

Mapping:
MAPPING LOAD DISTINCT
B,
C
FROM InitialData;

Next, use applymap:

Result:
LOAD
A,
applymap('Mapping', A, null()) as C
FROM InitialData;

Re: Search for equal field in other column

Anonymous — Fri, 10 Apr 2015 21:54:49 GMT

MapTable:

Mapping Load B as A,C From xxx;

LOAD A, ApplyMap('MapTable', A, null()) as C
From xxx;

Renaming B to A to have same column name, that's how mapping load works. ID column to match and the column to be retrieved

null() is the value to assign if there's no match

Re: Search for equal field in other column

Fri, 10 Apr 2015 22:12:12 GMT

Mapping load doesn't really care about field names. The first field is searched and the second one is the result.

Re: Search for equal field in other column

Anonymous — Fri, 10 Apr 2015 22:16:20 GMT

oh I see, thanks for the clarification, regards!

Re: Search for equal field in other column

buc_christian — Fri, 10 Apr 2015 22:24:12 GMT

hey guys, that sounds as a good idea, i try it out and then i ll give Feedback, hope it works,

thank you in advance

Re: Search for equal field in other column

buc_christian — Fri, 10 Apr 2015 22:43:20 GMT

Thanks to all. Solution works. Since i want to map more than one column in my original file, i do it with a join... works very well!!!

Ty all, again.