Help with strategy to look for different value in a list
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.
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.
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.
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.
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.
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?
