Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
while loading
Load
NUM,
TYPE_ITEM,
TYPE_ITEM as Type
FROM
source.....;
-Sundar
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);
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
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
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;
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
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
Hi,
Try usinig applymap, which is similar to vlookup in excel.
Regards
KC
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