Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
kavieweg
Partner
Partner

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
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
Partner
Partner
Author

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
Partner
Partner
Author

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
Partner
Partner
Author

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
Partner
Partner
Author

yes

sunny_talwar

Can you share the exact script you are using?