How to extract country column

[vanir88]

Hi,

i have a requirement like 

if [contract type] =MNon agreement then [country] should be [Contract country] column else [contract name] column

here (contract country, contract name, contract type ) are existing columns

country column is new column it has to create based on below condition.

eg: in below table  [Contract type] = "MNonAgreement" then [country] should take as 'ind', for rest country column shuld take from Contract name column

country	contracting country	contract name	contract type
	ind		Nonagreement
		us	ICclosureAgreement
		uk	ICclosureAgreement
		ind	ICclosureAgreement
	ind		MNonAgreement
	Germany		MNonAgreement
	ind		MNonAgreement
	ind		MNonAgreement

[durgesh22]

Hi ,

Please refer below.

tab1:
load * inline [
contracting_country,contract_name,contract_type
ind,'',Nonagreement
'',us,ICclosureAgreement
'',uk,ICclosureAgreement
'',ind,ICclosureAgreement
ind,'',MNonAgreement
Germany,'',MNonAgreement
ind,'',MNonAgreement
ind,'',MNonAgreement
];
NoConcatenate
tab2:
load *,if(wildMatch(contract_type,'MNonAgreement'),contracting_country,contract_name) as Country Resident tab1;
drop table tab1;

[vanir88]

no, if we have 5 or 10 columns then we can give manally but we have 1000s.... records so we cont add manually right

[vanir88]

can anyone help on this please

[MayilVahanan · Accepted solution]

Hi

@durgesh22 provided the end result with your sample data. However, you can implement the logic in your original script

Logic:                    

if(contract_type = 'MNonAgreement',contracting_country,contract_name) as Country