Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
If( Index( DateField, '*'), 'Star','NoStar')
If( Index( DateField, '*'), 'Star','NoStar')
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
QV manual/help does elaborate it nicely.
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
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,
HTH,
PFA,
Hirish
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
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.