Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Now in your straight table you can use the expression
SUM({$<Is_Valid={1}>} Values)
or just
SUM(Values * Is_Valid)
if (ApplyMap( 'Mapping_Table', [Column Name], 'Error')='Error' , 'Error' ,sum(Values) As TEST,
Hi,
Try this:
Sum(if (ApplyMap( 'Mapping_Table', [Column Name]), Values)) as Test
Where are you trying Applymap()? In Values table?
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 |
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.
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.
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
Now in your straight table you can use the expression
SUM({$<Is_Valid={1}>} Values)
or just
SUM(Values * Is_Valid)