topic Re: URGENT HELP!!! in QlikView

URGENT HELP!!!

Thu, 06 Feb 2014 11:35:26 GMT

Field Name

Value

SERIAL NUMBER

Example - A12B34

Contract Flag

This field will have potentially 3 values

C – Under contract based on Contract Start Date and Contract End Date

N – No contract based on the same dates

P – Used to have contracts but does not have any contracts now

For example, the serial number A12B34, has a contract number 0012345678 , which has validity of 04/12/12 to 04/11/13, in this case, the flag will have P as this serial number does not have a contract right now but it used to have in the past.

I got 'C and 'N' but unable to get the 'P'...How to achieve it....Thanks in advance!!!

Re: URGENT HELP!!!

manojkulkarni — Thu, 06 Feb 2014 11:37:42 GMT

didn't understand the problem, could elaborate the problem..

Re: URGENT HELP!!!

juleshartley — Thu, 06 Feb 2014 11:39:24 GMT

More detail please...!

Re: URGENT HELP!!!

Thu, 06 Feb 2014 12:19:58 GMT

Output:

Field Name	Value
SERIAL NUMBER	A12B34
Contract Flag	P

My Contract flag shud have the above three values as C,N,P which I need to be created.

For example,

Serial Number Contract Start Date End Date Contract flag

A12B34 12345 04/23/2012 04/23/2013 P

B12D34 54321 04/23/2012 04/23/2014 C

T12B35 22345 04/23/2012 04/23/2012 N

P->displays all serial numbers ie., A12B34 because it used to have a validity but now it has expired as of today.

C->displays all serial numbers ie., B12D34 because it is still valid as of today.

N->displays all serial numbers ie., T12B35 because it start date is equal to end date.

Re: URGENT HELP!!!

Thu, 06 Feb 2014 12:20:32 GMT

Reply given

Re: URGENT HELP!!!

puttemans — Thu, 06 Feb 2014 12:38:13 GMT

I'd make your end date a serial number by 'Num(End Date) as End Date' in the script,

Then you can play around with it:

If((End Date = Start date), N,

If ((End Date = Today()), C,

If((End Date < Today()), P))) as Flag

Re: URGENT HELP!!!

ashwanin — Thu, 06 Feb 2014 13:31:28 GMT

I think you need to add values as per your serial id, which must contain P,C, N

You can also get it from Serial number of contract.

if(left([Serial Number],1)='A','P ',if(left([Serial Number],1)='B','C',if(left([NUMBER],1)='T','N')) as Flag

Re: URGENT HELP!!!

Thu, 06 Feb 2014 13:40:30 GMT

Yes, I already tried that before, not getting the value P

Re: URGENT HELP!!!

Thu, 06 Feb 2014 13:41:31 GMT

this is not what i need, johan is nearer to my issue

Re: URGENT HELP!!!

juleshartley — Thu, 06 Feb 2014 14:10:13 GMT

Can you explain why Johan's answer does not work?

Re: URGENT HELP!!!

puttemans — Thu, 06 Feb 2014 14:30:43 GMT

I've sampled myself, based upon the three dates you haven it shows me. It shows 'N' and 'P', as the dates do not correspond to today (the requisite for 'C'. The small change I had to make was to put the N/C/P between ' ' in order to work.

Re: URGENT HELP!!!

Thu, 06 Feb 2014 14:33:32 GMT

Hope this makes sense...

Re: URGENT HELP!!!

Thu, 06 Feb 2014 14:34:24 GMT

please check the image which i attached.

Re: URGENT HELP!!!

Thu, 06 Feb 2014 14:40:56 GMT

please check the attached image. The problem is with 'P' because all the contracts which are having a start and end date will fall under 'C' category whereas for 'P' the end dates should be compared with today's date to find whether the end date has exceeded the today's date or not. See the image.

Re: URGENT HELP!!!

juleshartley — Thu, 06 Feb 2014 15:11:01 GMT

This should just be down to the ordering in your conditional clause-

=If([Contract End D]= [Contract Start],'N',(

If([Contract End D]<Today(),'P',(

'C')))

Re: URGENT HELP!!!

Thu, 06 Feb 2014 15:31:53 GMT

Still not getting the 'P' value 😞

Re: URGENT HELP!!!

Thu, 06 Feb 2014 15:40:26 GMT

Yes, its done now...