Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with values and in the load script i have a list with the Translation of the value
Order | Value 1 |
A | 1 |
B | 5 |
C | 7 |
D | 9 |
E | 0 |
F | 9 |
G | 1 |
Translation
0 | off |
1 | Speed |
5 | Break |
7 | Fast |
9 | Start |
Now I want the text in the Value 1 column
Order | Value 1 |
A | Speed |
B | Break |
C | Fast |
D | Start |
E | off |
F | Start |
G | Speed |
How can I reach this?
May be using ApplyMap and Mapping Load
MappingTable:
Mapping
LOAD * INLINE [
F1, F2
0, off
1, Speed
5, Break
7, Fast
9, Start
];
Fact:
LOAD Order,
ApplyMap('MappingTable', [Value 1], Null()) as [Value 1];
LOAD * INLINE [
Order, Value 1
A, 1
B, 5
C, 7
D, 9
E, 0
F, 9
G, 1
];
But I have already loaded a Table from a database where I have Value 1 as a field. that I want to replace
Yes, so what is the problem?
I have something like that:
[BI_DEPOT]:
LOAD
Text([DEPOT_ID]) as [Depot],
[ID],
[MODTIME] AS [BI_DEPOT.MODTIME],
[COUNTRY_CODE],
APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([COUNTRY_CODE]), '-') AS [BI_DEPOT.COUNTRY_CODE_GeoInfo];
SQL SELECT "DEPOT_ID",
"ID",
"MODTIME",
"COUNTRY_CODE"
FROM "TEST_DB"."BI_DEPOT";
Now I want to replace ID with F2 from the mapping table for the case that ID Matches F1
Something like this
MappingTable:
Mapping
LOAD * INLINE [
F1, F2
0, off
1, Speed
5, Break
7, Fast
9, Start
];
[BI_DEPOT]:
LOAD
Text([DEPOT_ID]) as [Depot],
ApplyMap('MappingTable', [ID], Null()) as [ID],
[MODTIME] AS [BI_DEPOT.MODTIME],
[COUNTRY_CODE],
APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([COUNTRY_CODE]), '-') AS [BI_DEPOT.COUNTRY_CODE_GeoInfo];
SQL SELECT "DEPOT_ID",
"ID",
"MODTIME",
"COUNTRY_CODE"
FROM "TEST_DB"."BI_DEPOT";
hmm... I am getting an error 😞
ApplyMap error:
map_id not found
You did add the MappingTable before the BI_DEPOT load, right?
yes
Can you share the exact script you are using?