Help with strategy to look for different value in a list

[#1]

Hi there,

I am looking for some help for this project, I am new to QV so if you could give me some ideas, function to try, etc it will be very useful.

Goal: Capture change in Reagent1 through time (sorted by date)

The data from the database look like the following table:

ID	Date	Reagent1
P3506-BAR	1/14/2016 0:19	NR0054_176355.029
P3506-BAR	1/14/2016 0:19	NR0054_176355.029
P3507-BAR	1/14/2016 11:39	NR0054_176355.029 NR0054_176355.033
P3521-BAR	1/15/2016 11:52	NR0054_176355.006 NR0054_176355.005
P3531-BAR	1/15/2016 23:57	NR0054_176358.160
P3531-BAR	1/15/2016 23:57	NR0054_176358.160
P3555-BAR	1/17/2016 13:55	NR0054_176359.015
P3555-BAR	1/17/2016 13:55	NR0054_176359.015
P3555-BAR	1/17/2016 13:55	NR0054_176359.015
P3555-BAR	1/17/2016 13:55	NR0054_176359.015
P3571-BAR	1/18/2016 12:37	NR0054_176355.161 NR0054_176355.103
P3574-BAR	1/18/2016 12:37	NR0054_176355.161 NR0054_176355.103
P3575-BAR	1/18/2016 14:52	NR0054_176355.103
P3575-BAR	1/18/2016 14:52	NR0054_176355.103

1) In some entries, I have multiple item separated by a space but only the first item is important.

2) Not all the string is relevant, only the red part NR0054_176355.029. I could use left(Reagent1, 13) to capture only the first item with the relevant string to compare.

3) I want to return the date and ID when left(Reagent1, 13) is changing chronologically.

With this example, the final output should be:

ID	Date	Reagent1
P3506-BAR	1/14/2016 0:19	NR0054_176355
P3531-BAR	1/15/2016 23:57	NR0054_176358
P3555-BAR	1/17/2016 13:55	NR0054_176359
P3571-BAR	1/18/2016 12:37	NR0054_176355

I still haven't set my mind on how to present the data in the end but having the final list will be a great start! I attached more data just in case.

Thanks for your help,

[#2]

Yes, let me try first, I think I can get the last value with Peek. I just need to find out about how to do the count with the range. You could help by only naming the function so I can look it up in the help section.

Thanks

Christian

[sunny_talwar]

I think for counting you might need to do a left join with a group by statement or another possible way is to use RangeSum(Peek('Count'), 1) kind of approach. I don't fully understand the requirement yet, so can't say what would be the best approach. But I would explore the first option first.

[#4]

I am still working on the new requirement but I would like to loop your script through all field, for example reagent2, reagent3, etc. Let me know how to structure the loop.

Thanks

[sunny_talwar]

I am not sure why you need to loop? Can you may be elaborate on what you are trying to do?

[#6]

If you look in the table I attached in the original post, I have the field reagent1 and reagent2, I would like to do this also for the other fields because in my real data-set, I have about 20 different fields where I need to peek.

[sunny_talwar]

So are we just creating flags now and not deleting those rows where the flag = 0? because I think reagent1 and reagent2 might have flag as 1 at different places. Which one of those need to be kept?