instead of null i want values

[Srinivas]

Hi All,

See below data, Here i have Num,Item_Type. and Type

Type have null values so instead of null values i want ITEM_TYPE values.

NUM	ITEM_TYPE	Type
17721	fast food	-
17722	fast food	-
17723	fast food	-
17724	fast food	-
17725	fast food	-
17726	fast food	-
17727	fast food	-
17728	fast food	-
17729	fast food	-

Regards

Munna

[sundarakumar]

while loading

Load

NUM,

TYPE_ITEM,

TYPE_ITEM as Type

FROM

source.....;

-Sundar

[JonnyPoole]

The values in your spreadsheet are not actually null , they are the character '-'.  So to load ITEM_TYPE when the character is '-' then use this expression in the loads script.

LOAD NUM,

    ITEM_TYPE,

    if( Type = '-',ITEM_TYPE, Type) as Type

FROM

(ooxml, embedded labels, table is Sheet1);

But, if you are dealing with actual NULL values, then use this:

LOAD NUM,

    ITEM_TYPE,

    if(  isnull(Type) = -1 ,ITEM_TYPE, Type) as Type

FROM

(ooxml, embedded labels, table is Sheet1);

[Srinivas]

sorry for this i forget one thing, both are different table like see below then what should i do. Could you please help on this.

First Table:

NUM	ITEM_TYPE
17721	fast food
17722	fast food
17723	fast food
17724	fast food
17725	fast food
17726	fast food
17727	fast food
17728	fast food
17729	fast food
17730	fast food
17731	fast food
17732	fast food

Second table:

NUM	Type
17721	-
17722	-
17723	-
17724	-
17725	-
17726	-
17727	-
17728	-
17729	-
17730	-
17731	-
17732	-

Regards

Munna

[aveeeeeee7en · Accepted solution]

Hi

Use this Condition in your Script:

if(IsNull(Type) OR Type='-',ITEM_TYPE,Type) AS New_Type

Modify your Script as follows:

Table1:
LOAD * INLINE [
NUM, ITEM_TYPE
17721, fast food
17722, fast food
17723, fast food
17724, fast food
17725, fast food
17726, fast food
17727, fast food
17728, fast food
17729, fast food
17730, fast food
17731, fast food
17732, fast food
];
Left Join(Table1)

LOAD * INLINE [
NUM, Type
17721, -
17722, -
17723, -
17724, -
17725, -
17726, -
17727, -
17728, -
17729, -
17730, -
17731, -
17732, -
];

MainTable:
NoConcatenate
Load *,
if(IsNull(Type) OR Type='-',ITEM_TYPE,Type) AS New_Type
Resident Table1;
DROP Table Table1;

See the Result:

Also, see the Attachment.

Regards

Av7eN

[sujeetsingh]

Load Num,ItemType

from Table1

Left join Table1

Num, Type

Final Data:

Load Num,ItemType

,if(Type=null(),ItemType as Type

resident Table 1;

drop table 1;

[sundarakumar]

u have 2 options

1. Join the tables, take a resident and do the transformation to replace null with the other type field.

2. Use applymap (which will map the first entry in case the key has multiple entries)

-sundar

[Siva_Sankar]

Munna,

First of all the excel column Type is not null. it is showing '-'. You remove that. If you want to load item type in type only if it is null, you can use applymap function and mapping load. find the attached document

[jyothish8807]

Hi,

Try usinig applymap, which is similar to vlookup in excel.

Regards

KC

[#10]

hi

try any one, hope this helps you.

if( Type = ' ',ITEM_TYPE, Type) as Type

if( Type = '-',ITEM_TYPE, Type) as Type

if( Type = '',ITEM_TYPE, Type) as Type