Wild characters in Qlikview

[#1]

I am having a date column in which some of the dates are having * in the end like 22-Dec-2015*

and some dates are not having * like 22-Aug-2011.

Now I wanna identify them in a column having flag as (22-Dec-2015*)Star & (22-Aug-2011)NoStar.

How do I do this  ?

Thanks!

[swuehl · Accepted solution]

If( Index( DateField, '*'), 'Star','NoStar')

[sunny_talwar]

May be like this:

LOAD DateField,

          If(KeepChar(DateField, '*') = '*', 'Star', 'NoStar') as Flag,

          Date(Date#(Left(DateField, 11, 'DD-MMM-YYYY'), 'DD-MMM-YYYY') as Date

FROM Source

[#4]

Thanks swuehl‌

It works

Can you please elaborate the use of Index function ?

Regards

Swapneel

[tresesco]

QV manual/help does elaborate it nicely.

index(s1 , s2[ , n])

Position of a substring. This function gives the starting position of the n:th occurrence of substring s2 in string s1. If n is omitted, the first occurrence is assumed. If n is negative, the search is made starting from the end of string s1. The result is an integer. The positions in the string are numbered from 1 and up.

Examples:

index( 'abcdefg', 'cd' ) returns 3

index( 'abcdabcd', 'b', 2 ) returns 6

index( 'abcdabcd', 'b', -2 ) returns 2

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

[HirisH_V7]

Hi,

Like this way,

Date:
LOAD *,
If(Right(Date,1)='*','Star','NonStar') as DateFlag
INLINE [
    Date
    12-02-2015*
    13-02-2015
    14-02-2015*
    15-02-2015
    16-02-2015*
    17-02-2015
    18-02-2015*
];

Check this too,

Inter-Record Functions

HTH,

PFA,

Hirish

[#7]

Thanks all for the wondeful replies.

Qlik community is a blessing to all Qlikview developers.

I need one more help, in a step behind the * thing of above.

I have 2 date fields, Date1 & Date2.

Expression: If(IsNull(Date2),Date1,Date2&'*')

     the above function works fine for almost 98% of all the lines, but in some lines what I am getting is just the Date2 but not the *.

Why so ? any idea, if I take it as a flag of true and false, all works fine, but not the *

P.S: Apologies for I know this explaination might not be best way to elaborate, but I will do it with comments as needed.

Thanks

[tresesco]

Try expression like:

If(Len(Trim(Date2))=0,Date1,Date2&'*')

Sometimes, there would be values (like space or so) which are not really NULL, but you wish that to be considered so. The above expression takes care of that.

[#9]

Great @ tresesco‌

Thanks It worked pefectly

Regards,

Swapneel