Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kavieweg
New Contributor II

Match Text to a Value

I have a table with values and in the load script i have a list with the Translation of the value

  

Order Value 1
A1
B5
C7
D9
E0
F9
G1

   Translation

0off
1Speed
5Break
7Fast
9Start

Now I want the text in the Value 1 column

  

Order Value 1
ASpeed
BBreak
CFast
DStart
Eoff
FStart
G

Speed

How can I reach this?

11 Replies
MVP
MVP

Re: Match Text to a Value

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
New Contributor II

Re: Match Text to a Value

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

MVP
MVP

Re: Match Text to a Value

Yes, so what is the problem?

kavieweg
New Contributor II

Re: Match Text to a Value

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

MVP
MVP

Re: Match Text to a Value

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
New Contributor II

Re: Match Text to a Value

hmm... I am getting an error :-(

ApplyMap error:
map_id not found

MVP
MVP

Re: Match Text to a Value

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

kavieweg
New Contributor II

Re: Match Text to a Value

yes

MVP
MVP

Re: Match Text to a Value

Can you share the exact script you are using?

Community Browser