Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
pala_jagadeesh
Contributor III
Contributor III

if or where clause condition

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
Labels (4)
4 Solutions

Accepted Solutions
G3S
Creator III
Creator III

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.

View solution in original post

sidhiq91
Specialist II
Specialist II

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

View solution in original post

G3S
Creator III
Creator III

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

G3S_2-1660719864044.png

 

the result is:

G3S_3-1660719919455.png

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)

 

View solution in original post

Iswarya_
Creator
Creator

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;

Iswarya__0-1660720557159.png

 

View solution in original post

7 Replies
G3S
Creator III
Creator III

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
Specialist II
Specialist II

@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
Contributor III
Contributor III
Author

@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
Contributor III
Contributor III
Author

HI @G3S

can you please elaborate in details, with example

 

G3S
Creator III
Creator III

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

G3S_2-1660719864044.png

 

the result is:

G3S_3-1660719919455.png

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_
Creator
Creator

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;

Iswarya__0-1660720557159.png

 

pala_jagadeesh
Contributor III
Contributor III
Author

@Iswarya_ @G3S @sidhiq91

Thank you all