Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Check this new code:
Table:
LOAD ID,
Date,
SubField(Reagent1, '.', 1) as New
FROM
[New reagents_QV help.xls]
(biff, embedded labels, table is Sheet1$);
TempTable:
LOAD *,
If(Peek('New') = New, 0, 1) as Flag
Resident Table
Order By Date, New;
FinalTable:
LOAD ID,
Date,
New as Reagent1
Resident TempTable
Where Flag = 1;
DROP Tables Table, TempTable;
I am not sure I understand the logic behind your output. Can you elaborate please?
We have a batch of reagent assigned to a specific lot, the lot number structure it 'Part Number'_'Lot Number'.Serial (example NR0054_176355.029). The reagent are consumed in the laboratory and I want to know when a new lot is used. See table for what I am looking for. It will simplify the expession if we ignore the re-introduction of an existing lot (example P3571-BAR).
Is is possible to look for distinct value and return the one with the earliest date?
ID | Date | Reagent1 | |
P3506-BAR | 1/14/2016 0:19 | NR0054_176355.029 | First lot (New lot) |
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 | New lot |
P3531-BAR | 1/15/2016 23:57 | NR0054_176358.160 | |
P3555-BAR | 1/17/2016 13:55 | NR0054_176359.015 | New lot |
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 | Change in lot, but was used on 1/14 |
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 |
Try this:
Table:
LOAD ID,
Date,
Reagent1,
SubField(Reagent1, '.', 1) as New
FROM
[https://community.qlik.com/thread/212866]
(html, codepage is 1252, embedded labels, table is @1);
TempTable:
LOAD *,
If(Peek('New') = New, 0, 1) as Flag
Resident Table
Order By Date;
FinalTable:
LOAD ID,
Date,
New as Reagent1
Resident TempTable
Where Flag = 1;
DROP Tables Table, TempTable;
Seems to be a good start, it gave me the 3 different lots for this example. I will try on an other data set and look into more details at your proposal. I will come back if I get blocked for the next steps.
Awesome! Thanks
Sounds like a plan. Please keep us posted
I have been dissecting the code. The flag is not done properly. Do you experience the same situation?
Thanks
Date | ID | New | Flag | Reagent1 | |
1/14/2016 12:19:24 AM | P3506-BAR | NR0054_176355 | 1 | NR0054_176355.029 | OK |
1/14/2016 12:19:24 AM | P3506-BAR | NR0054_176355 | 0 | NR0054_176355.029 | |
1/14/2016 11:39:09 AM | P3507-BAR | NR0054_176355 | 0 | NR0054_176355.029 NR0054_176355.033 | |
1/14/2016 11:39:09 AM | P3508-BAR | NR0054_176355 | 0 | NR0054_176355.029 NR0054_176355.033 | |
1/14/2016 11:39:09 AM | P3509-BAR | NR0054_176355 | 0 | NR0054_176355.029 NR0054_176355.033 | |
1/14/2016 5:54:36 PM | P3514-BAR | NR0054_176355 | 0 | NR0054_176355.033 NR0054_176355.134 | |
1/14/2016 5:54:36 PM | P3515-BAR | NR0054_176355 | 0 | NR0054_176355.033 NR0054_176355.134 | |
1/14/2016 5:54:36 PM | P3516-BAR | NR0054_176355 | 0 | NR0054_176355.033 NR0054_176355.134 | |
1/14/2016 5:54:36 PM | P3517-BAR | NR0054_176355 | 0 | NR0054_176355.033 NR0054_176355.134 | |
1/14/2016 7:29:37 PM | P3510-BAR | NR0054_176355 | 0 | NR0054_176355.033 | |
1/14/2016 7:29:37 PM | P3511-BAR | NR0054_176355 | 0 | NR0054_176355.033 | |
1/14/2016 7:29:37 PM | P3512-BAR | NR0054_176355 | 0 | NR0054_176355.033 | |
1/14/2016 7:29:37 PM | P3513-BAR | NR0054_176355 | 0 | NR0054_176355.033 | |
1/15/2016 2:09:20 AM | P3518-BAR | NR0054_176355 | 0 | NR0054_176355.134 NR0054_176355.006 | |
1/15/2016 2:09:20 AM | P3519-BAR | NR0054_176355 | 0 | NR0054_176355.134 NR0054_176355.006 | |
1/15/2016 2:09:20 AM | P3520-BAR | NR0054_176355 | 0 | NR0054_176355.134 NR0054_176355.006 | |
1/15/2016 11:52:28 AM | P3521-BAR | NR0054_176355 | 0 | NR0054_176355.006 NR0054_176355.005 | |
1/15/2016 11:52:28 AM | P3522-BAR | NR0054_176355 | 0 | NR0054_176355.006 NR0054_176355.005 | |
1/15/2016 11:52:28 AM | P3523-BAR | NR0054_176355 | 0 | NR0054_176355.006 NR0054_176355.005 | |
1/15/2016 11:52:28 AM | P3524-BAR | NR0054_176355 | 0 | NR0054_176355.006 NR0054_176355.005 | |
1/15/2016 1:28:27 PM | P3525-BAR | NR0054_176355 | 0 | NR0054_176355.005 | |
1/15/2016 1:28:27 PM | P3526-BAR | NR0054_176355 | 0 | NR0054_176355.005 | |
1/15/2016 1:28:27 PM | P3527-BAR | NR0054_176355 | 0 | NR0054_176355.005 | |
1/15/2016 1:28:27 PM | P3528-BAR | NR0054_176355 | 0 | NR0054_176355.005 | |
1/15/2016 11:57:29 PM | P3529-BAR | NR0054_176355 | 1 | NR0054_176355.160 | Wrong |
1/15/2016 11:57:29 PM | P3529-BAR | NR0054_176355 | 0 | NR0054_176355.160 | |
1/15/2016 11:57:29 PM | P3530-BAR | NR0054_176355 | 0 | NR0054_176355.160 | |
1/15/2016 11:57:29 PM | P3531-BAR | NR0054_176358 | 1 | NR0054_176358.160 | OK |
1/15/2016 11:57:29 PM | P3531-BAR | NR0054_176358 | 0 | NR0054_176358.160 |
Check this new code:
Table:
LOAD ID,
Date,
SubField(Reagent1, '.', 1) as New
FROM
[New reagents_QV help.xls]
(biff, embedded labels, table is Sheet1$);
TempTable:
LOAD *,
If(Peek('New') = New, 0, 1) as Flag
Resident Table
Order By Date, New;
FinalTable:
LOAD ID,
Date,
New as Reagent1
Resident TempTable
Where Flag = 1;
DROP Tables Table, TempTable;
Yes perfect! That was a good exercise for me, I was trying to do it in a pivot table, load script is easier.
Now I want to count how many ID was used for each New and put starting and ending date for each.
Cheer,
Christian
You plan to attempt the new requirement on your own first, right?