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??

[OmarBenSalem]

He're the logic behind it:

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,Concat(ApplyMap('map',Value),',') as Value Group by Interactivity_data_type_id
;
load Interactivity_data_type_id, SubField(Value,'-') as Value //In ur case SubField(Value,',') Inline [
Interactivity_data_type_id,Value

1,Abc

2,Def

3,4-3-2

4,Xyz

5,mno
];

Result

[Nikhil2725]

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

[OmarBenSalem]

if u had an ID 6 with value 4,2 , u don't want to apply this logic??

[Nikhil2725]

Yea, 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...

[OmarBenSalem]

if that is the case:

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 Inline [
Interactivity_data_type_id,Value

1,Abc

2,Def

3,4-3-2

4,Xyz

5,mno
6, 4-2
];

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;

result:

[Nikhil2725]

Hi OmarBenSalem,

I have replaced the Inline function with the DB load script, however I failed to get the desired result.

Interactivity:
LOAD `interactivity_data_type_id`,
value;
SQL SELECT `interactivity_data_type_id`,
value
FROM rcdbprod.`INTERACTIVITY_DATA`;


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
Resident Interactivity;

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;

[OmarBenSalem]

That's not what I've done.

Please try to EXACTLY recreate what I've done.

[Vegar]

I believe that it will be easier to use the mapsubstring function. There might be some syntax issues hence I'm replying from my phone, but try/examine the script below.

map:
mapping Load * Inline [
Value,New
1,Specific(Not Realistic)
2,Realistic(Not Specific)
3, Both Specific and Realistic
4,Not Specific or Realistic
];
load
Interactivity_data_type_id,
MapSubString('map', Value) as Value
inline [
Interactivity_data_type_id,Value
1,Abc
2,Def
3,"4,3,2"
4,Xyz
5,mno
];

Read more about MapSubString here:
https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFun...

[Nikhil2725]

Hi OmarBenSalem,
Thanks for the reply..
Can u please tell me how to replace INLINE function with my DB script??