numbers into values

[Nikhil2725]

Hi All,

I have a database where the values will be stored as the numeric values in one interactivity_data_type_id(3).

Interactivity_data_type_id	Value
1	Abc
2	Def
3	4,3,2
4	Xyz
5	mno

For those numeric value text are as below.

1-Specific(Not Realistic)

2-Realistic(Not Specific)

3- Both Specific and Realistic

4-Not Specific or Realistic

Now I want to display the relevant text for those numeric value s below.

Interactivity_data_type_id	Value
1	Abc
2	Def
3	Not Specific or Realistic, Both Specific and Realistic, Realistic(Not Specific)
4	Xyz
5	mno

Kindly help me in displaying as above.. Can I do in front end ? If possible how can I do that??

[Nikhil2725]

Hello,
I have just replaced the INLINE function by the DB script..
Kindly help me where im doing wrong..
map:

mapping Load * Inline
[
Value,New
1,Specific(Not Realistic)

2,Realistic(Not Specific)

3, Both Specific and Realistic

4,Not Specific or Realistic
];

Table:

load interactivity_data_type_id, SubField(Value,',') as Value;

LOAD `interactivity_data_type_id`,
value as Value;
SQL SELECT `interactivity_data_type_id`,
value
FROM rcdbprod.`INTERACTIVITY_DATA`;


Finale:

NoConcatenate
load interactivity_data_type_id,Concat(ApplyMap('map',Value),',') as Value

Resident Table Where interactivity_data_type_id=3 Group by interactivity_data_type_id;

load interactivity_data_type_id, concat(Value,',') as Value

Resident Table Where interactivity_data_type_id<>3 Group by interactivity_data_type_id;

drop Table Table;

[Vegar]

map:
mapping Load * Inline [
Value,New
1, Specific(Not Realistic)
2, Realistic(Not Specific)
3, Both Specific and Realistic
4, Not Specific or Realistic
];

Table:
load
  Interactivity_data_type_id,
  MapSubString('map', value) as Value
;
SQL SELECT 
 interactivity_data_type_id,
 value
FROM 
   rcdbprod.INTERACTIVITY_DATA
;

[Nikhil2725]

Hi Vegar,
One more thing is like I just want to put this logic only for the Interactivity_data_type_id=3.
Thanks in advance

[Vegar]

I can interpret your need into two different scenarios, choose one of them
based on your needs. I've added both adjustments to the script below.

1. Add a where clause to your SQL to limit which data you include into your
application.

2. Add an if statement around your mapping to limit which rows that should
have this logic.

map:
mapping Load * Inline [
Value,New
1, Specific(Not Realistic)
2, Realistic(Not Specific)
3, Both Specific and Realistic
4, Not Specific or Realistic
];

Table:
load
  Interactivity_data_type_id,
  If(Interactivity_data_type_id= 3,MapSubString('map', value) )as Value
;
SQL SELECT 
 interactivity_data_type_id,
 value
FROM 
   rcdbprod.INTERACTIVITY_DATA
WHERE
  interactivity_data_type_id = 3 
;

[Nikhil2725]

Hi
Thanks for the reply..

I don want to put this logic for any other ID's..
Only for the particular ID 3 i want to convert the numeric value into words.
In the other words, if i have numeric value in ID 1 or 2 or 4, I have to display only the relevant numeric value...

[Vegar]

You could include the 3 into the mapping key like in the solution below. 

map:
mapping Load 
'3|'& Value as Value, 
New
Inline [
Value,New
1, Specific(Not Realistic)
2, Realistic(Not Specific)
3, Both Specific and Realistic
4, Not Specific or Realistic
];

Table:
load
  Interactivity_data_type_id,
  MapSubString('map', Interactivity_data_type_id &'|'& value) )as Value
;
SQL SELECT 
 interactivity_data_type_id,
 value
FROM 
   rcdbprod.INTERACTIVITY_DATA
;

[Nikhil2725]

Hi Vegar ,
I tried the code which you have given. But for my luck it didnt worked.. Not getting the desired results