-1

I want to identify the bad/invalid records so that i can add in a separate SQL Table. For example, we have an account object. And i want to find bad accounts. But i need to apply some filters on contact object. If conditions satisfy based on contact then i want to inserts those invalid account records in SQL Table.

I don't want to directly query from contact. I want to query using account but conditions should be used from contact.

Do anyone knows what is the best way to perform loop in Pentaho? Check each record for contact , if all contact's condition satisfy then add Account id in table. If one of the contact record doesn't satisfy condition. The relevant account should not be added in SQL Table

For Example:

On Account "A" we have 10 contacts if the email field is empty on all 10 contacts then add Account in SQL table(As bad data) if on two of contact rcords has email field populated but 8 of them are blank then Account id shouldn't be added in SQL table

How we can better implement this scenario using Pentaho? Any help matters

Thanks

Tayyaba Noor
  • 1
  • 1

1 Answers1

0

So you can create a transformation similar to this:

Transformation example

  • You have a query with the different account contacts
  • Order the query data by account
  • Group the information by accounts and calculate the maximum ContactMail (so if all mails in contacts are null, the max will be a null, is the result of that step is shown in the Preview data part of my screenshot)
  • Filter rows by MaxContactMail IS NOT NULL

These could be the basic steps, you'll need to add more steps or perform more than one transformation depending on the complexity of your data.

Ana GH
  • 1,397
  • 1
  • 9
  • 19
  • Ana - Thank you soo much for your detail answer. This is the solution which i was looking. i got my answer Definitely I will try to implement this solution (Y) – Tayyaba Noor Sep 14 '22 at 15:25