if or where clause condition

[pala_jagadeesh]

Hi i have three columns like below

id	s1	s2
abc	india	japan
bca	america	china
cda	australia	russia

how to write condition for id-abc  (india) will not talk with japan otherwise he can talk with any other country like

                                                      id-bca (america) will not talk with china otherwise he can talk with any other country like

                                                      id-cda (australia) will not talk with russia otherwise he can talk with any other country like

output

id	s1	s2	communicate
abc	india	japan	no
bca	america	china	no
cda	australia	russia	no

[G3S · Accepted solution]

assuming a list of who can't talk to who is being loaded in another table (lets name it 'rule'), create a key : s1&s2 in both tables. 

then if key from your table which has the id matches the key from the 'rule' table, it will pick up the value from 'communicate' field. 

 you can have expression that says if no value found from 'rules' table, means the s1&s2 combination can communicate, otherwise no communication.

[sidhiq91 · Accepted solution]

@pala_jagadeesh  Please see the below code that I have used. 

NoConcatenate
Temp:
Load * Inline [
id, s1, s2
abc, india, japan
bca, america, china
cda, australia, russia
];

NoConcatenate
Temp1:
Load *,
if(id='abc' and Match(s1,'india') and match(s2,'japan'),'No',
if(id='bca' and Match(s1,'america') and match(s2,'china'),'No',
if(id='cda' and Match(s1,'australia') and match(s2,'russia'),'No','Yes'))) as Communicate
Resident Temp;
Drop table Temp;

Exit Script;

If that does not resolve your issue, please share some more sample data and let us know what is required.

[pala_jagadeesh]

@sidhiq91

Hi,

here i mention three records, so you mention values,

but if more than 1000 records are present , how can we write condition here

[pala_jagadeesh]

HI @G3S

can you please elaborate in details, with example

[G3S · Accepted solution]

I have added few more id in my load.  (don't need key like I mentioned before. sorry)

the result is:

if you prefer not to load it  separate field, can stop at row 21 in the script and in the front end have an expression for 'comms'.   =if(isnull(comms),'Can Communicate',comms)

[Iswarya_ · Accepted solution]

Data:
Load * Inline [
id, s1, s2
abc, india, japan
bca, america, china
cda, australia, russia
efg, india, china
];

Temp:
Mapping Load
s1&s2 as Key,
'No' as communicate
Inline [
id, s1, s2
abc, india, japan
bca, america, china
cda, australia, russia

];

Final:
Load id,
s1&s2 as Key,
ApplyMap('Temp',s1&s2,'Yes') as communicate
Resident Data;
Drop table Data;

[pala_jagadeesh]

@Iswarya_ @G3S @sidhiq91

Thank you all