Thanks Peter for your help %OR will help

Expression="%OR({[Demographics].[H10].[Citizenship].&[USA],[Demographics].[H10].[Citizenship].&[U.S.A],[Demographics].[H10].[Citizenship].&[US]})"   

Can you advice how to use the same expression to say if citizenship value is not one of these values something like this NOT %OR?

Hi @Dmitry Maslennikov 
This suggestion worked thanks, one I am looping over 60 columns and all is working fine. One thing is breaking sometimes data is single codes ' in the text and such records are not being saved. Any suggestion how to handle this?

For i=1:1:60 {
set FieldName = FieldName_",Field"_i
set FieldVaue = FieldVaue_"','"_$Piece(pInput,"|",i)}

our version is 2019.1 so these new options not available, any other thoughts?

thanks can i also use not in like below

Select * from HS_Registry.Patient where Facility='ABC' and MPIID NOT IN

(select MPIID from HS_Registry.Patient where Facility='XYZ')

Thanks will do some R&D on these methods, is there transaction concept here where I begin transaction and while loading SDAs any error faces the entire transaction got rolled back ?

yes correct i tried dynamic sql and it worked

This didn’t work I would need to write 60 lines for each column Field9, Field10 I can generate the code lines using excel but it’s just like doesn’t look good. Any idea you can give that will help

For i=2:1:$LENGTH(line) {

set SourceTable.Field_i = $PIECE(line, "|", i)

Thanks will try, is there a way to run queries on globals like get distinct values from allergy code across all patients?

also now data is stored in SDA from flat file can I query SAD.Allergy to get distinct allergy codes and validate how many patients have allergy record but with no allergy code value provided in flat files?

Hi Vitaly, this is existing code written by earlier team, linenumber+1 is there, the code first create globals and then from globals push the data to ECR. One question for reconciliation purpose I can create one table and push data to that table to i can store the flat files data into a table for example "SourceData" so I can do some validation on data prior to pushing to ECR like mandatory fields are not empty, code fields have allowed codes etc. 

I can do that 

Set  table.field1 = $PIECE(line, "|", 1)

Set  table.field1 = $PIECE(line, "|", 1)

set status = table.%save() 

OR

I can use your For logic above to go over the each value in the line but setting to table field can I use "i" variable like

For i=2:1:$LENGTH(line) {

set SourceTable.Field_i = $PIECE(line, "|", i)

Thanks, your code gave me the idea, Do you think below code will fix the issue and ".line" value will be changed? I will do testing but it will take time so review new code below pls and advice if anything wrong is there.

while ('tInput.AtEnd)   {
       #dim line as %String = tInput.ReadLine(, .status)
       quit:$$$ISERR(status)
       set tLineNumber = tLineNumber + 1
       //20112020: new code added
       Set remainder = $PIECE(line, "|", 9, $LENGTH(line))
       Set newline = $PIECE(line, "|", 2)_"^"_$PIECE(line, "|", 3)_"^"_$PIECE(line, "|", 4)_"^"_$PIECE(line, "|", 5)_"^"_$PIECE(line, "|", 6)_"^"_$PIECE(line, "|", 7)_"^"_$PIECE(line, "|", 8)_remainder
       Set line = newline
       //20112020: new code end
       if tLineNumber > 1       {
        If (tFileType="PatientContact")    {
        set status = ..SetPatientContact(.line, tFileType, tLineNumber)

Thanks @Adrian Zeeman  

How about Not Contains  syntax and Lookup syntax