topic Re: Replacing value using Calculation in QlikView

Replacing value using Calculation

Fri, 25 Oct 2013 10:33:18 GMT

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

Re: Replacing value using Calculation

Peter_Cammaert — Fri, 25 Oct 2013 10:38:20 GMT

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

Re: Replacing value using Calculation

MayilVahanan — Fri, 25 Oct 2013 10:45:21 GMT

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

];

Re: Replacing value using Calculation

Fri, 25 Oct 2013 10:50:44 GMT

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"

Re: Replacing value using Calculation

israrkhan — Fri, 25 Oct 2013 10:52:45 GMT

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

Re: Replacing value using Calculation

Fri, 25 Oct 2013 11:05:37 GMT

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

Re: Replacing value using Calculation

israrkhan — Fri, 25 Oct 2013 11:26:20 GMT

Hi.

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

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