topic Re: if(ApplyMap() ...do something in QlikView

if(ApplyMap() ...do something

bobbydave — Tue, 07 Apr 2015 09:44:51 GMT

Mapping_Table:

Mapping

LOAD [Column Name],

[Table Name]

FROM

(ooxml, embedded labels, table is Act_MaterialFields);

Values:

Load

[Table Name],

[Column Name],

Age,

Name,

Values

FROM (qvd);

I have the above tables and what I want to do is to say

if (ApplyMap( 'Mapping_Table', [Column Name], 'Error'), sum(Values) as Test

You see, I am not really looking for a value to be returned as such from the Mapping_Table; I want to see if the Column Name values matches the Mapping_Tables Column Name and if so, sum the Values in the Values table, else check the next entry in Column Name and check to see if that is in the Mapping Table and sum the Values

When I do the above test with my ApplyMap, I get Invalid Expression.

Any help appreciated.

Re: if(ApplyMap() ...do something

DavidFoster1 — Tue, 07 Apr 2015 09:53:27 GMT

if (ApplyMap( 'Mapping_Table', [Column Name], 'Error')='Error' , 'Error' ,sum(Values) As TEST,

Re: if(ApplyMap() ...do something

mrooney — Tue, 07 Apr 2015 09:59:41 GMT

Hi,

Try this:

Sum(if (ApplyMap( 'Mapping_Table', [Column Name]), Values)) as Test

Re: if(ApplyMap() ...do something

anbu1984 — Tue, 07 Apr 2015 10:11:28 GMT

Where are you trying Applymap()? In Values table?

Re: if(ApplyMap() ...do something

bobbydave — Tue, 07 Apr 2015 10:20:02 GMT

Would you even use ApplyMap() for what I want to do because I dont want to return the corresponding value; if my table looks like this

Mapping_Table:

Table Name	Column Name
A	Red
B	Blue
C	Orange
D	Green

Values

Table Name	Column Name	Age	Values
A	Red	1	112
Z	Black	2	113
S	Grey	3	114
C	Orange	4	115
B	Blue	5	116
D	Green	6	117
Q	White	7	118

So in Values table, if Column Name matches something in the Mapping table, sum the Values in the Value table that is associated with what values that have been returned from the ApplyMap function and so the following table should be returned

Table Name	Column Name	Age	Values
A	Red	1	112
C	Orange	4	115
B	Blue	5	116
D	Green	6	117
			Sum Only Above Values

Re: if(ApplyMap() ...do something

bobbydave — Tue, 07 Apr 2015 10:21:32 GMT

Yes, in the Values table.

I need to match what is in Values table Column Name with the Mapping Table (just to see if these values match) and then to sum the result.

Re: if(ApplyMap() ...do something

anbu1984 — Tue, 07 Apr 2015 10:32:26 GMT

Why do you want to sum the values? Do you have duplicates?

You can simply use Inner join to remove the rows where column name not present in Mapping table.

Re: if(ApplyMap() ...do something

DavidFoster1 — Tue, 07 Apr 2015 10:42:43 GMT

Ah I understand what you want to do now.

Yes applymap is still used but in your load script and then the result is reference in set analysis in your straight table. You also need to appreciate that a mapping table only has 2 columns (1 key & 1 result). I am guessing from your example that you were treating both columns as keys. The approach there is to create a compound key (and autonumber it if you are dealing with BIG data)

Mapping_Table:

Mapping

LOAD

[Table Name] & '-' & [Column Name] AS [Key],

1 AS [Is_Valid] //these field names are not used but help explain what is going on

FROM

(ooxml, embedded labels, table is Act_MaterialFields);

Values:

Load

[Table Name],

[Column Name],

APPLYMAP('Mapping_Table',[Table Name] & '-' & [Column Name],0) AS [Is_Valid],

Age,

Name,

Values

FROM (qvd);

Now in your straight table you can use the expression

SUM({$<Is_Valid={1}>} Values)

or just

SUM(Values * Is_Valid)