Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
After loading it in your data model resident load the fields with nested if statement.
You can use it in UI as well
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...;
Gysbert he have to also see the latest date
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.
Mayank,
Here you need to use max() function to also check the latest date.
See the sample
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)