jeudi 30 juillet 2020

Concatenate results from 2 or more conditions - Tableau

I'm developing a dashboard in Tableau but I have an issue which I don't know how to solve it. I need your help :D

My objective is to have a better visualization because we are working in a customer and contacts datacleansing and I need to validate each record of the database, I mean, some fields doesn't have to have special character like "! # $ % & /"

Previousy I created some calculated fields like these in order to validate if the mailing street address has or not the special character

[z Mailing Street !] = IF CONTAINS(MailingStreetField, '|' ) THEN 1 ELSE 0 END
[z Mailing Street #] = IF CONTAINS(MailingStreetField, '#' ) THEN 1 ELSE 0 END
[z Mailing Street $] = IF CONTAINS(MailingStreetField, '$' ) THEN 1 ELSE 0 END
[z Mailing Street %] = IF CONTAINS(MailingStreetField, '|' ) THEN 1 ELSE 0 END

After I created all the fields, I want to have something like this

|Mailing street contains $ + = >| 

So I created a new calculated field:

IF [z Mailing Street empty] = 1 THEN '|Mailing street vacío|' 
  ELSEIF [z Mailing Street] > 0 THEN
  '|Mailing street contains ' +
  IF [z Mailing Street !] = 1 THEN '! ' END +
  IF [z Mailing Street #] = 1 THEN '# ' END +
  IF [z Mailing Street $] = 1 THEN '$ ' END +
  IF [z Mailing Street %] = 1 THEN '% ' END +
  IF [z Mailing Street &] = 1 THEN '& ' END +
  IF [z Mailing Street (] = 1 THEN '( ' END +
  IF [z Mailing Street )] = 1 THEN ') ' END +
  IF [z Mailing Street ?] = 1 THEN '? ' END +
  IF [z Mailing Street @] = 1 THEN '@ ' END +
  IF [z Mailing Street \] = 1 THEN '\ ' END +
  IF [z Mailing Street £] = 1 THEN '£ ' END +
  IF [z Mailing Street €] = 1 THEN '€ ' END +
  IF [z Mailing Street +] = 1 THEN '+ ' END +
  IF [z Mailing Street <] = 1 THEN '< ' END +
  IF [z Mailing Street =] = 1 THEN '= ' END +
  IF [z Mailing Street >] = 1 THEN '> ' END +
  '|'
END

But at the end I got the result only if all the conditions are true, I mean, Tableau evaluates the sentences like this:

IF 
  Condition = True AND 
  Condition2 = True AND 
  Condition3= True 
  THEN Result 
END

Any feedback will be realy appreciated.

Thanks

Aucun commentaire:

Enregistrer un commentaire