Logic Needed

DS2016 · Post by **DS2016** » Fri Jun 03, 2016 8:13 am

Hi All,

I have an incoming file with 4 fields. I need to check each value in field1 has 10 specific values in field3. If atleast one is missing from field3, file should not be processed.

Can someone please help me to achieve this.

Appreciate your help.

Thanks

ArndW · Post by **ArndW** » Fri Jun 03, 2016 9:19 am

Could you give a short example of the data in fields 1 and 3?

DS2016 · Post by **DS2016** » Fri Jun 03, 2016 9:46 am

Below is an example of data. I have made up some data. Field2, even if it has 10 or 20 distinct values, it should definitely have the 10 distinct values that I am looking for.

Field1 Field2

1CP001 ABC
1CP001 BCD
1CP001 CDE
1CP001 DEF
1CP001 EFG
1CP001 FGH
1CP001 GHI
1CP001 HIJ
1CP001 IJK
1CP001 JKL
1CP002 ABC
continues like this

chulett · Post by **chulett** » Fri Jun 03, 2016 1:04 pm

Your original post mentions that you have "four fields" and the logic needs to check field1 against field3... and now your example has two fields. So, is field2 in your example the same as field3 in your first post?

And are you looking for 10 distinct values regardless of actual value or does your "distinct values that I am looking for" comment mean you have a list of 10 specific values that need to be there or the file "should not be processed"? We'll probably need to talk about what exactly that means but let's start with this.

Not a big fan of made up example data, btw.

UCDI · Post by **UCDI** » Fri Jun 03, 2016 1:16 pm

this sounds like something you would do easier in SQL, counting the values in one column to see if you have exactly 10 distinct values in the second column.

So if they are in a file, poke them into a temporary staging table, run a sql statement that validates it, and proceed could be a simple design?

If it passes that test you can check that each value is in a specific list with a lookup stage to finish up the checking, if I understood the requirement.

Teej · Post by **Teej** » Mon Jun 06, 2016 12:42 pm

Or just use a lookup against a row generator with list of reference data.

Nice thing about Row Generator, you can use parameters in some cases to achieve what you want.

Also could use a sequential file, if you can manage that (some customers do not like the idea of a random text file in the production environment).

-T.J.

chulett · Post by **chulett** » Mon Jun 06, 2016 1:41 pm

I'd still like to see the requirements clarified.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 06, 2016 4:04 pm

I'd be thinking to use a Data Rules stage implementing in_reference_column rules.