Replacing value using Calculation

[#1]

Hi All,

I am scanning a filed in a table for numeric value using IsNum function. The condition is if the value is not a number then the value should be changed with a value from different table:

Table 1
A	B
1	aaa
2	bbb
3	ccc

Table 2
A	C
1	00001
2	00002
3	Null

In Table 2, row number 3 has a NULL value and hence it should be replaced with ccc from Table 3.

The query I am using in Table 2 is:

if(isnum(C)=-1,C,B) as New_Filed

Thanks in Advance

[Peter_Cammaert]

Make Table 1 into a mapping table.

Then replace your expression in Table 2 with

if (IsNum(C), C, applymap('Table1',A)) as New_Field

However the IsNum() function may have trouble detecting numerical values in a mixed-type column.

Peter

[MayilVahanan]

HI

Try like this

Table1:

Mapping

Load * Inline

[

A,B

1,aaa

2,bbb

3,ccc

];

Table2:

LOAD *, if(IsNum(C), C, ApplyMap('Table1',A, 0)) As B;

LOAD * Inline

[

A,C

1,00001

2,00002

3,Null

];

[#4]

Hi Peter,

Following is our query for Map Load:

A1:

Mapping Load

A,B

Resident Table1;

And we used your query to replace the value/ Now we are getting an error which state "File B not found"

[israrkhan]

Hi,

1) In your table 2, C Values 00001, 00002 are not numeric value, these are text values,

numeric or integer value can not have 0 before any digit, like 002 will be equal to 2, and when you will check

if(c=2), i will return false... if c contain 002.

2) If you are reading data from sql server, and when you will check if(c= -1), and if c contain NULL,

it will not return you true, or false , it will return you Unknown.

so isnum() will not work in this case...

[#6]

Hi Guys,

Based upon the reply given by Israr, I have to change my Table 2 as follows:

Table 2
A	C
1	762
2	145
3

[israrkhan]

Hi.

add another condition to check null in your existing condition as below

if (isnum(c )) OR IF(ISNULL(C)).