Match Text to a Value

[kavieweg]

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?

[sunny_talwar]

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

];

[kavieweg]

But I have already loaded a Table from a database where I have Value 1 as a field. that I want to replace

[sunny_talwar]

Yes, so what is the problem?

[kavieweg]

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

[sunny_talwar]

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";

[kavieweg]

hmm... I am getting an error 😞

ApplyMap error:
map_id not found

[sunny_talwar]

You did add the MappingTable before the BI_DEPOT load, right?

[kavieweg]

yes

[sunny_talwar]

Can you share the exact script you are using?