Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2016-04-11 03:57 PM

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 DateReagent1
P3506-BAR1/14/2016 0:19NR0054_176355.029
P3506-BAR1/14/2016 0:19NR0054_176355.029
P3507-BAR1/14/2016 11:39NR0054_176355.029 NR0054_176355.033
P3521-BAR1/15/2016 11:52NR0054_176355.006 NR0054_176355.005
P3531-BAR1/15/2016 23:57NR0054_176358.160
P3531-BAR1/15/2016 23:57NR0054_176358.160
P3555-BAR1/17/2016 13:55NR0054_176359.015
P3555-BAR1/17/2016 13:55NR0054_176359.015
P3555-BAR1/17/2016 13:55NR0054_176359.015
P3555-BAR1/17/2016 13:55NR0054_176359.015
P3571-BAR1/18/2016 12:37NR0054_176355.161 NR0054_176355.103
P3574-BAR1/18/2016 12:37NR0054_176355.161 NR0054_176355.103
P3575-BAR1/18/2016 14:52NR0054_176355.103
P3575-BAR1/18/2016 14:52NR0054_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 DateReagent1
P3506-BAR1/14/2016 0:19NR0054_176355
P3531-BAR1/15/2016 23:57NR0054_176358
P3555-BAR1/17/2016 13:55NR0054_176359
P3571-BAR1/18/2016 12:37NR0054_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,

Preview file
163 KB
1,484 Views
0 Likes
1 Solution

Accepted Solutions
sunny_talwar
MVP
MVP
‎2016-04-12 04:36 PM

In response to

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;

View solution in original post

Community_212866_v1.qvw
963 Views
0 Likes
15 Replies
sunny_talwar
MVP
MVP
‎2016-04-11 04:19 PM

I am not sure I understand the logic behind your output. Can you elaborate please?

963 Views
0 Likes
Not applicable
‎2016-04-11 06:13 PM
Author

In response to sunny_talwar

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?

    

IDDateReagent1
P3506-BAR1/14/2016 0:19NR0054_176355.029 First lot (New lot)
P3506-BAR1/14/2016 0:19NR0054_176355.029
P3507-BAR1/14/2016 11:39NR0054_176355.029 NR0054_176355.033
P3521-BAR1/15/2016 11:52NR0054_176355.006 NR0054_176355.005
P3531-BAR1/15/2016 23:57NR0054_176358.160New lot
P3531-BAR1/15/2016 23:57NR0054_176358.160
P3555-BAR1/17/2016 13:55NR0054_176359.015New lot
P3555-BAR1/17/2016 13:55NR0054_176359.015
P3555-BAR1/17/2016 13:55NR0054_176359.015
P3555-BAR1/17/2016 13:55NR0054_176359.015
P3571-BAR1/18/2016 12:37NR0054_176355.161 NR0054_176355.103Change in lot, but was used on 1/14
P3574-BAR1/18/2016 12:37NR0054_176355.161 NR0054_176355.103
P3575-BAR1/18/2016 14:52NR0054_176355.103
P3575-BAR1/18/2016 14:52NR0054_176355.103
963 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-04-11 06:27 PM

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;

Community_212866.qvw
963 Views
0 Likes
Not applicable
‎2016-04-11 09:37 PM
Author

In response to sunny_talwar

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

963 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-04-12 08:05 AM

In response to

Sounds like a plan. Please keep us posted

963 Views
0 Likes
Not applicable
‎2016-04-12 03:29 PM
Author

In response to sunny_talwar

sunindia‌

I have been dissecting the code. The flag is not done properly. Do you experience the same situation?

Thanks

   

DateIDNewFlagReagent1
1/14/2016 12:19:24 AMP3506-BARNR0054_1763551NR0054_176355.029OK
1/14/2016 12:19:24 AMP3506-BARNR0054_1763550NR0054_176355.029
1/14/2016 11:39:09 AMP3507-BARNR0054_1763550NR0054_176355.029 NR0054_176355.033
1/14/2016 11:39:09 AMP3508-BARNR0054_1763550NR0054_176355.029 NR0054_176355.033
1/14/2016 11:39:09 AMP3509-BARNR0054_1763550NR0054_176355.029 NR0054_176355.033
1/14/2016 5:54:36 PMP3514-BARNR0054_1763550NR0054_176355.033 NR0054_176355.134
1/14/2016 5:54:36 PMP3515-BARNR0054_1763550NR0054_176355.033 NR0054_176355.134
1/14/2016 5:54:36 PMP3516-BARNR0054_1763550NR0054_176355.033 NR0054_176355.134
1/14/2016 5:54:36 PMP3517-BARNR0054_1763550NR0054_176355.033 NR0054_176355.134
1/14/2016 7:29:37 PMP3510-BARNR0054_1763550NR0054_176355.033
1/14/2016 7:29:37 PMP3511-BARNR0054_1763550NR0054_176355.033
1/14/2016 7:29:37 PMP3512-BARNR0054_1763550NR0054_176355.033
1/14/2016 7:29:37 PMP3513-BARNR0054_1763550NR0054_176355.033
1/15/2016 2:09:20 AMP3518-BARNR0054_1763550NR0054_176355.134 NR0054_176355.006
1/15/2016 2:09:20 AMP3519-BARNR0054_1763550NR0054_176355.134 NR0054_176355.006
1/15/2016 2:09:20 AMP3520-BARNR0054_1763550NR0054_176355.134 NR0054_176355.006
1/15/2016 11:52:28 AMP3521-BARNR0054_1763550NR0054_176355.006 NR0054_176355.005
1/15/2016 11:52:28 AMP3522-BARNR0054_1763550NR0054_176355.006 NR0054_176355.005
1/15/2016 11:52:28 AMP3523-BARNR0054_1763550NR0054_176355.006 NR0054_176355.005
1/15/2016 11:52:28 AMP3524-BARNR0054_1763550NR0054_176355.006 NR0054_176355.005
1/15/2016 1:28:27 PMP3525-BARNR0054_1763550NR0054_176355.005
1/15/2016 1:28:27 PMP3526-BARNR0054_1763550NR0054_176355.005
1/15/2016 1:28:27 PMP3527-BARNR0054_1763550NR0054_176355.005
1/15/2016 1:28:27 PMP3528-BARNR0054_1763550NR0054_176355.005
1/15/2016 11:57:29 PMP3529-BARNR0054_1763551NR0054_176355.160Wrong
1/15/2016 11:57:29 PMP3529-BARNR0054_1763550NR0054_176355.160
1/15/2016 11:57:29 PMP3530-BARNR0054_1763550NR0054_176355.160
1/15/2016 11:57:29 PMP3531-BARNR0054_1763581NR0054_176358.160OK
1/15/2016 11:57:29 PMP3531-BARNR0054_1763580NR0054_176358.160
963 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-04-12 04:36 PM

In response to

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;

Community_212866_v1.qvw
964 Views
0 Likes
Not applicable
‎2016-04-12 06:58 PM
Author

In response to sunny_talwar

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

963 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-04-12 08:03 PM

In response to

You plan to attempt the new requirement on your own first, right?

963 Views
0 Likes