Create a Flag

[#1]

Hello,

I have a file that consists of list of products used for testing in an industry.

Every-time a product comes into the inventory, its activity is recorded as 'IN' with date and time.

Afterwards if any worker takes that product out for testing it is termed as 'OUT'. When he returns that product back, its activity is marked with a 'RETURN' value.

SAMPLE RECORDS:

ProductNo           Date                Time           Activity

T1                     02/24/2014      08:52:00AM      OUT

T1                     02/24/2014      08:51:00AM      IN

T1                     03/12/2014      07:30:51AM      OUT

T1                     03/12/2014      07:30:41AM      RETURN

T3                     04/16/2014      07:22:41AM      RETURN

T3                     04/10/2014      12:25:00PM      OUT

T3                     04/10/2014      12:22:00PM      IN

T2                     05/21/2014      10:01:25AM      OUT

T2                     05/21/2014      10:00:15AM      IN

T4                     06/01/2014      08:10:20AM      RETURN

T4                     06/01/2014      08:05:00AM      IN

T5                     06/11/2014      06:20:00PM      IN

All the rows are unsorted, and don't have any unique record no.

Now I need to create a flag, which checks availability of the product in the inventory, on basis of :

i) if last record entered for a product has Activity equal to RETURN or IN, then its available (1)

ii) if Activity is OUT and it is not returned back, then its unavailable (0)

Expected output is:

ProductNo           Date           Time             Activity      AvailabilityFlag

T1                     02/24/2014 08:52:00AM      OUT             0

T1                     02/24/2014 08:51:00AM      IN                0

T1                     03/12/2014 07:30:51AM      OUT             0

T1                     03/12/2014 07:30:41AM      RETURN      0

T3                     04/16/2014 07:22:41AM      RETURN      1

T3                     04/10/2014 12:25:00PM      OUT             0

T3                     04/10/2014 12:22:00PM      IN                0

T2                     05/21/2014 10:01:25AM      OUT             0

T2                     05/21/2014 10:00:15AM      IN                0

T4                     06/01/2014 08:10:20AM      RETURN      1

T4                     06/01/2014 08:05:00AM      IN                0

T5                     06/11/2014 06:20:00PM      IN                1

Can anyone help me with this please?

[sujeetsingh]

After loading it in your data model resident load the fields with nested if statement.

You can use it in UI as well

[Gysbert_Wassenaar]

I don't understand your result table.

T4                     06/01/2014 08:10:20AM      RETURN      1

T4                     06/01/2014 08:05:00AM      IN                0

Why is T4 unavailable at 08:05:00?

If RETURN and IN mean available I'd do it like this:

LOAD ProductNo,

          Date,

          Time,

           Activity,

          if(match(Activity,'RETURN','IN'),1,0) as fAvailable

FROM ...mysource...;

[sujeetsingh]

Gysbert he have to also see the latest date

[#5]

Thanks sujeet for quick reply..
Can you please provide a small example to how to create that nested if based on latest date.

And Yes, you are right on Gysbert question.

[sujeetsingh]

Mayank,

Here you need to use max() function to also check the latest date.

See the sample

[sujeetsingh · Accepted solution]

Sorry i missed it,

Here it is .

Note for time and date i have used a field as Status now the flag is defined as

i) if last record entered for a product(with maximum status assigned) has Activity equal to RETURN or IN, then its available (1)