topic Re: Create a Flag in QlikView

Create a Flag

Tue, 24 Jun 2014 09:56:13 GMT

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?

Re: Create a Flag

sujeetsingh — Tue, 24 Jun 2014 10:03:49 GMT

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

You can use it in UI as well

Re: Create a Flag

Gysbert_Wassenaar — Tue, 24 Jun 2014 10:07:49 GMT

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...;

Re: Create a Flag

sujeetsingh — Tue, 24 Jun 2014 10:24:11 GMT

Gysbert he have to also see the latest date

Re: Create a Flag

Tue, 24 Jun 2014 11:28:47 GMT

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.

Re: Create a Flag

sujeetsingh — Tue, 24 Jun 2014 11:30:59 GMT

Mayank,

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

See the sample

Re: Create a Flag

sujeetsingh — Tue, 24 Jun 2014 11:45:51 GMT

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)