if(ApplyMap() ...do something

[bobbydave]

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.

[DavidFoster1]

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

[mrooney]

Hi,

Try this:

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

[anbu1984]

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

[bobbydave]

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

[bobbydave]

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.

[anbu1984]

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.

[DavidFoster1 · Accepted solution]

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)